You are almost there. You just missed the brackets.

CREATE Procedure dbo.GetQueryRowsWithSQL
@SQL varchar (500)
As
EXEC (@SQL)
return @@RowCount
GO

Rohtash Kapoor
http://www.sqlmantra.com

[quoted text, click to view]

I need to pass a completely built SQL statement to a stored procedure as a
string parameter and have the stored procedure execute it. Sort of like
this:
CREATE Procedure dbo.GetQueryRowsWithSQL
@SQL varchar (500)
As
EXEC @SQL
return @@RowCount
GO

Close, but not quite. Anybody done this?

Thanks,
Dean Slindee

Hi Dean,

Thank you for using MSDN Newsgroup! It's my pleasure to assist you with
your issue.

I agree with Greg that this stored procedure will have security problems as
SQL injection. SQL injection is the name for a general class of attacks
that can allow nefarious users to retrieve data, alter server settings, or
even take over your server if you're not careful. SQL injection is not a
SQL Server problem, but a problem with improperly written applications.

Please refer to the following article:
The 'Step 10. SQL Server Security' in the following article 'Securing Your
Database Server':
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnnetsec/ht
ml/thcmch18.asp

The SQL injection part in article 'Building Secure Data Access' :
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnnetsec/ht
ml/thcmch14.asp

Hope this helps. If you still have questions, please feel free to post new
message here and I am ready to help

Best regards

Baisong Wei
Microsoft Online Support
----------------------------------------------------
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only. Thanks.

Hi Dean.

Just beware that you've considered the possibility of "sql injection"
security violations when designing a stored procedure like this. There's
much written up on this topic already, so I won't regurgitate it here - if
you're not sure about sql injection, do a search & read up as this type of
procedure is very dangerous in an application if this isn't considered in
advance. Essentially, just make sure that there's no way a user could impact
what sql is passed to that parameter - eg typing "My Name;delete from
customers" into a text field in a web page..

Regards,
Greg Linwood
SQL Server MVP

[quoted text, click to view]

[posted and mailed, please reply in news]

Dean Slindee (slindee@mindspring.com) writes:
[quoted text, click to view]

In addition to Greg's and Baisong's comments, permit me to be nosy and
ask: why do you need this? This is completely pointless. You could just
as well send the @sql statement from the client directly, and skip the
stored procedure. The stored procedure buys you *nothing* in this case.


--
Erland Sommarskog, SQL Server MVP, sommar@algonet.se

Books Online for SQL Server SP3 at