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 2008 Forums
 Transact-SQL (2008)
 Need Query to pass dyanmic query to select stmt

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.
Go to Top of Page

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.
Go to Top of Page

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.
Go to Top of Page

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.
Go to Top of Page

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 Shaw
SQL Server MVP
Go to Top of Page

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 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'.
Go to Top of Page

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 + @qry
sp_ExceuteSQL(@actQry)
This is now you should be executing the query
Go to Top of Page

Sivangari
Starting Member

16 Posts

Posted - 2010-08-20 : 02:55:01
Hi,

Thanks for sharing your idea.
Go to Top of Page
   

- Advertisement -