Re: Dynamic SQL

From: Jonathan Chong (jonathan_at_3exp.com)
Date: 09/29/04 

Date: Wed, 29 Sep 2004 11:07:20 +0800

Use sp_executesql.

DECLARE @i_count int
EXEC sp_executesql N'select @i_count = count(*) from users',N'@i_count int
OUTPUT', @i_count OUTPUT
SELECT @i_count

"John Byrne" <John Byrne@discussions.microsoft.com> wrote in message
news:5AE4F137-8F84-4C98-8E09-9BEFCF79B760@microsoft.com...
> Hi,
>
> I'm having problem getting the value from a query using dynamic SQL.
>
> Normally you can do the following:
>
> set @i_count = (select count(*) from users)
> or
> select @i_count = count(*) from users
>
> but how do you do it with dynamic sql?
>
> if you try:
>
> set @i_count = exec('select count(*) from users')
>
> you get error: Incorrect syntax near the keyword 'exec'
>
> if you try:
>
> exec('select @i_count = count(*) from users')
>
> you get error: Must declare the variable '@i_count'
>
> So, how do you get the resulting value from a dynamic SQL statement??
>
> I need to use dynamic sql so I change the sql to get the data from another
> database or another database on another server.
>
> Thanks.
>

Relevant Pages

  • Re: DB name as parameter
    ... Ruling out using EXEC and Dynamic SQL has ... The need to pass the database name would raise the question of why it is ...
    (microsoft.public.sqlserver.programming)
  • RE: USE Command With Dynamic Database Names Fails to Change Database
    ... The statement USE does not accept a variable, so you have to use dynamic sql ... and execute the batch using EXEC or SP_EXECUTESQL. ... EXEc or SP_EXECTESQL. ... > The first command is successful and creates the new database, ...
    (microsoft.public.sqlserver.programming)
  • RE: Looping through databases in stored proc
    ... You'll need to use dynamic SQL. ... name in your EXEC statement. ... > dynamically run sp_helpfile against each database on the server. ...
    (microsoft.public.sqlserver.programming)
  • Re: Convert string to Number
    ... of dynamic sql. ... create table formula(idd int identity(1,1),c1 varchar) ... exec exec_formulae ... Vishal Parkar ...
    (microsoft.public.sqlserver.mseq)
  • Re: Variable Column Name in SELECT
    ... Dynamic sql with output parameter ... exec sp_executesql @dynamicsql, N'@TotalRecords int output', @TotalRecords ...
    (microsoft.public.sqlserver.programming)