| Author |
 Topic  |
|
|
Sivangari
Starting Member
India
16 Posts |
 Posted - 08/19/2010 : 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
USA
15568 Posts |
Posted - 08/19/2010 : 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
India
16 Posts |
Posted - 08/19/2010 : 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
USA
15568 Posts |
Posted - 08/19/2010 : 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 - 08/19/2010 : 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
Flowing Fount of Yak Knowledge
South Africa
4507 Posts |
Posted - 08/19/2010 : 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 Shaw
SQL Server MVP |
|
|
|
Sivangari
Starting Member
India
16 Posts |
Posted - 08/20/2010 : 01:31:48
|
Hi,
i have a stored procedure like
create procedure CustomFilter
@qry nvarchar(max),
@channelId bigint
As
BEGIN
select * from orders where ChannelId=@channelId and @qry
END
And the @qry will be another condition like 'shipingstatus='processed''.
i couldnt create the procedure ,it throws me exception as
Msg 4145, Level 15, State 1, Procedure CustomFilter, Line 9
An expression of non-boolean type specified in a context where a condition is expected, near 'END'.
|
|
|
|
Shaila24072009
Starting Member
India
7 Posts |
Posted - 08/20/2010 : 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 + @qry
sp_ExceuteSQL(@actQry)
This is now you should be executing the query
|
|
|
|
Sivangari
Starting Member
India
16 Posts |
Posted - 08/20/2010 : 02:55:01
|
Hi,
Thanks for sharing your idea.
|
|
|
| |
Topic |
|
Need Query to pass dyanmic query to select stmt
