Stored Proc that receives part sql as paramter

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Stored Proc that receives part sql as paramter

Author

Topic

adlo
Posting Yak Master

108 Posts

Posted - 2010-06-16 : 12:45:35

I have a stored proc that receives part as parameter the WHERE clause of a query.

So simplified i need to return the result of the 2nd query


DECLARE @SQL nvarchar(4000),@SQL2 nvarchar(4000),@SQL3 nvarchar(4000),@myCount int
SET @SQL='1=1'
SET @SQL2 ='SELECT 2 WHERE '+@SQL
EXEC sp_executesql @SQL2
SET @SQL3 ='SELECT 3 WHERE 2=2'--using SQL2 result
EXEC sp_executesql @SQL3

The above returns
2
3

I only want it to return 3.

I cannot declare a temp variable and set it to a result of my initial query to reuse in my 2nd query since I concatenate the SQL in the 1st query and hence cannot use variables (I wish i could)

adlo
Posting Yak Master

108 Posts

Posted - 2010-06-16 : 12:51:49

Basically I want to use dynamic sql in a stored proc so sp_executesql is the only way I understand this can be done.
However using two queries . The 1st to get a value to be used in the second has an issue of returning two results.
Setting nocount on for the first does not work

Anyone any ideas?

robvolk
Most Valuable Yak

15732 Posts

Posted - 2010-06-16 : 12:59:45

Some comments:

1. "Basically I want to use dynamic sql in a stored proc so sp_executesql is the only way I understand this can be done." That's not entirely true, you can construct a string of SQL commands and use EXECUTE('SQL string'), but sp_executesql is preferred.

2. Using dynamic SQL like this pretty much defeats the purpose of having a stored procedure. You won't get any benefit having it as a procedure.

3. This is a security nightmare waiting to happen. No matter how well you scrub the values passed in, sooner or later some SQL injection will occur.

4. Your example is not a practical description of what you're trying to accomplish. It would be better to post specific details using real(istic) data along with the structure of the tables you're querying.

adlo
Posting Yak Master

108 Posts

Posted - 2010-06-16 : 13:10:29

Thanks you cleared up my mind.

The whole query was a bit complex so I will rethink the design.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources