Discussion Locked

SQL String in SQL Serv. Stored Procedure

0 helpful
3 replies
Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

Can a concatenated SQL string be used in a SQL Server Stored Procedure as it can in VB or MS Access VBA? In other words, in VB I can set a string
strSQL = "SELECT * FROM MyTable WHERE Name = " & Table.FieldName & ";"
Is there a way to do it in aStored Procedure so that variables can be imbedded which represent fieldnames which are in another table as entries.
CASE SELECT is not a reasonable alternative, since there are up to 50 different field names in the second table.

3 replies

by andrew

No problem.
Use Exec (execute) to run the built up SQL string:

Create Proc pListNames (@FieldName varchar(128),@FieldValue varchar(128))as
exec ('select * from authors where ' + @FieldName + ' = ' + "'" + @FieldValue + "'")

exec pListNames 'au_lname','white'

(If you're passing in string values, remember to wrap them in single quotes once they're in the proc)

The downside to this is that SQL Server can't compile the proc, so performance is poorer.

This discussion is closed to new replies.