Execute Statement..

[Arijit Chatterjee]

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

[John Bell]

Re: Execute Statement..

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


arijitchatterje e123@yahoo.co.i n (Arijit Chatterjee) wrote in message news:<ea01504d. 0310302205.2ca9 8658@posting.go ogle.com>...[color=blue]
> 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[/color]

[Arijit Chatterjee]

Re: Execute Statement..

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

[John Bell]

Re: Execute Statement..

See inline

"Arijit Chatterjee" <arijitchatterj ee123@yahoo.co. in> wrote in message
news:ea01504d.0 311012028.19b50 6fb@posting.goo gle.com...[color=blue]
> Create proc sp_test
> as
> Declare @query varchar{500)[/color]
This will not compile
[color=blue]
> Declare @var varchar(10)
> set @var='Test'
> Set @query = 'Select * from table' + ' Where ' + 'Colname = ' + @var[/color]
If you were doing this correctly the value in @var would be enquoted
[color=blue]
> exec (@query)----> Working fine
> if exists (exec (@query))----> Sending error
> print 'Hi'
> Now tell me how to solve this.[/color]

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.
[color=blue]
> Regards
> Arijit Chatterjee[/color]

John

[Clifford Schneider]

Re: Execute Statement..

What you need to do in this case is create a sql server temporary
table (such as create table #temp (column1 nvarchar(10),co lumn2
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" <jbellnewsposts @hotmail.com> wrote in message news:<bo2oar$89 l$1@hercules.bt internet.com>.. .[color=blue]
> See inline
>
> "Arijit Chatterjee" <arijitchatterj ee123@yahoo.co. in> wrote in message
> news:ea01504d.0 311012028.19b50 6fb@posting.goo gle.com...[color=green]
> > Create proc sp_test
> > as
> > Declare @query varchar{500)[/color]
> This will not compile
>[color=green]
> > Declare @var varchar(10)
> > set @var='Test'
> > Set @query = 'Select * from table' + ' Where ' + 'Colname = ' + @var[/color]
> If you were doing this correctly the value in @var would be enquoted
>[color=green]
> > exec (@query)----> Working fine
> > if exists (exec (@query))----> Sending error
> > print 'Hi'
> > Now tell me how to solve this.[/color]
>
> 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.
>[color=green]
> > Regards
> > Arijit Chatterjee[/color]
>
> John[/color]