469,090 Members | 1,163 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,090 developers. It's quick & easy.

Execute Statement..

Dear Friens,
I am writing a SP.But storeing a query in a variable.But at the time
of execution generating error.Exam
===================
Declare @query varchar{500)
Set @query = 'Select * from table'

if exists (exec (@query))
print 'Hi'
====================
But "if exists" line giving error.How do I solve this.Please help me
out.
Reagrds
Arijit Chatterjee
Jul 20 '05 #1
4 7670
Hi

EXISTS requires a sub-query as the test, and EXEC does not do that.

If you have to do this dynamically then you may want to look at
sp_executesql to return a count or move everything into @query.

If you posted more precise detail it may be easier to offer advice.

John
ar*****************@yahoo.co.in (Arijit Chatterjee) wrote in message news:<ea**************************@posting.google. com>...
Dear Friens,
I am writing a SP.But storeing a query in a variable.But at the time
of execution generating error.Exam
===================
Declare @query varchar{500)
Set @query = 'Select * from table'

if exists (exec (@query))
print 'Hi'
====================
But "if exists" line giving error.How do I solve this.Please help me
out.
Reagrds
Arijit Chatterjee

Jul 20 '05 #2
Create proc sp_test
as
Declare @query varchar{500)
Declare @var varchar(10)
set @var='Test'
Set @query = 'Select * from table' + ' Where ' + 'Colname = ' + @var
exec (@query)----> Working fine
if exists (exec (@query))----> Sending error
print 'Hi'
Now tell me how to solve this.
Regards
Arijit Chatterjee
Jul 20 '05 #3
See inline

"Arijit Chatterjee" <ar*****************@yahoo.co.in> wrote in message
news:ea**************************@posting.google.c om...
Create proc sp_test
as
Declare @query varchar{500) This will not compile
Declare @var varchar(10)
set @var='Test'
Set @query = 'Select * from table' + ' Where ' + 'Colname = ' + @var If you were doing this correctly the value in @var would be enquoted
exec (@query)----> Working fine
if exists (exec (@query))----> Sending error
print 'Hi'
Now tell me how to solve this.
Reading books online would be the first place to look.

Then read http://www.algonet.se/~sommar/dynamic_sql.html on why you should
justify the use of dynamic SQL.

You should then be able to work out how to return the count using
sp_executesql to get what is require.
Regards
Arijit Chatterjee


John
Jul 20 '05 #4
What you need to do in this case is create a sql server temporary
table (such as create table #temp (column1 nvarchar(10),column2
nvarchar(10))) and then build your @query string so that it inserts
the results of your dynamic select statement into the temp table. You
can then play the whole "exists" game on the results of a query
against your #temp table.

Make sure that you do not build your temp table dynamically. And
remember, you can't insert results from a dynamically built select
statement into a normal variable, but you can insert them into a temp
table.

Good Luck!

"John Bell" <jb************@hotmail.com> wrote in message news:<bo**********@hercules.btinternet.com>...
See inline

"Arijit Chatterjee" <ar*****************@yahoo.co.in> wrote in message
news:ea**************************@posting.google.c om...
Create proc sp_test
as
Declare @query varchar{500)

This will not compile
Declare @var varchar(10)
set @var='Test'
Set @query = 'Select * from table' + ' Where ' + 'Colname = ' + @var

If you were doing this correctly the value in @var would be enquoted
exec (@query)----> Working fine
if exists (exec (@query))----> Sending error
print 'Hi'
Now tell me how to solve this.


Reading books online would be the first place to look.

Then read http://www.algonet.se/~sommar/dynamic_sql.html on why you should
justify the use of dynamic SQL.

You should then be able to work out how to return the count using
sp_executesql to get what is require.
Regards
Arijit Chatterjee


John

Jul 20 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

1 post views Thread by PJ | last post: by
5 posts views Thread by Andrew | last post: by
1 post views Thread by rAinDeEr | last post: by
3 posts views Thread by Rahul Babbar | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.