SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Need Query to pass dyanmic query to select stmt
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Sivangari
Starting Member

India
16 Posts

Posted - 08/19/2010 :  09:50:00  Show Profile  Reply with Quote
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
15679 Posts

Posted - 08/19/2010 :  09:56:51  Show Profile  Visit robvolk's Homepage  Reply with Quote
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

India
16 Posts

Posted - 08/19/2010 :  09:59:22  Show Profile  Reply with Quote
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

USA
15679 Posts

Posted - 08/19/2010 :  11:13:20  Show Profile  Visit robvolk's Homepage  Reply with Quote
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 - 08/19/2010 :  11:38:43  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

South Africa
4507 Posts

Posted - 08/19/2010 :  16:26:15  Show Profile  Visit GilaMonster's Homepage  Reply with Quote
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

India
16 Posts

Posted - 08/20/2010 :  01:31:48  Show Profile  Reply with Quote
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

India
7 Posts

Posted - 08/20/2010 :  02:17:00  Show Profile  Reply with Quote
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

India
16 Posts

Posted - 08/20/2010 :  02:55:01  Show Profile  Reply with Quote
Hi,

Thanks for sharing your idea.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000