Author |
Topic |
Sivangari
Starting Member
16 Posts |
Posted - 2010-08-19 : 09:50:00
|
I need to pass the dynamic query to pass in select query as parameter.can anyone help me how to proceed. Thanks in Advance. |
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2010-08-19 : 09:56:51
|
If you're asking how to pass a SELECT query to a stored procedure as a parameter, just to execute it, then don't bother using a stored procedure. It defeats the purpose. Just execute the query from your application. |
|
|
Sivangari
Starting Member
16 Posts |
Posted - 2010-08-19 : 09:59:22
|
No,i need to pass the query in stored procedure.That query will be dynamic query . so i need to know how to pass the query to stored procedure and that query will be parameter for the stored procedure. |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2010-08-19 : 11:13:20
|
That is exactly what I meant. Again, don't bother using a stored procedure for this, it defeats the purpose. |
|
|
parody
Posting Yak Master
111 Posts |
Posted - 2010-08-19 : 11:38:43
|
if you really do need to do it in an SP, pass the statement in as a varchar(max) parameter and with the sp EXEC ot sp_executesql against the param.Careful with injection on this. |
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2010-08-19 : 16:26:15
|
Talk about a SQL injection nightmare. What happens the day someone hacks that and passes 'exec sp_ms_foreachtable ''drop ?''' as a parameter?I'm guessing you want this because there's a rule that all data access is via stored procedure. If so, rethink your methods, you may be following the word of the rule, but certainly not the intent--Gail ShawSQL Server MVP |
|
|
Sivangari
Starting Member
16 Posts |
Posted - 2010-08-20 : 01:31:48
|
Hi,i have a stored procedure like create procedure CustomFilter@qry nvarchar(max),@channelId bigintAsBEGINselect * from orders where ChannelId=@channelId and @qryENDAnd the @qry will be another condition like 'shipingstatus='processed''.i couldnt create the procedure ,it throws me exception asMsg 4145, Level 15, State 1, Procedure CustomFilter, Line 9An expression of non-boolean type specified in a context where a condition is expected, near 'END'. |
|
|
Shaila24072009
Starting Member
7 Posts |
Posted - 2010-08-20 : 02:17:00
|
You will not be able to execute the query directly by setting the value of the parameter. Instead generate the query and call sp_executeSQL(SQLStatement) to execute your query.In your case:declare @actQry varchar(255)set @actQry = 'select * from orders where ChannelId' set @actQry = @actQry + @qrysp_ExceuteSQL(@actQry)This is now you should be executing the query |
|
|
Sivangari
Starting Member
16 Posts |
Posted - 2010-08-20 : 02:55:01
|
Hi,Thanks for sharing your idea. |
|
|
|