sp_executesql syntax

12 Nov 2005 1:36 AM

Scott

In FIGURE 1, my code works fine, allowing dynamic fields to be passed. My
attempt in FIGURE 2 was to have 2 WHERE parameters and an IF test that
allows changing the @field and @result variables because different fields
will need different data types for a @result variable. I'm failing bad as
this is my first dealings using the sp_executesql sproc.

Can anyone help me structure FIGURE 2? The code runs on Northwind.

FIGURE 1:
declare @sql nvarchar(4000)
declare @orderID int, @sField sysname
declare @result int

set @orderID = 10248
set @sField = 'UnitPrice'

set @sql = 'SELECT @result = SUM([' + @sField + ']) FROM [Order Details]
WHERE orderID = @orderID'

exec sp_executesql @sql, N'@orderID int, @result int output', @orderID,
@result output
print @result

FIGURE 2:

declare @sql nvarchar(4000), @paramlist nvarchar(4000)
declare @sField sysname
declare @dataType int
declare @orderID int, @productID int

set @dataType = 1

IF @dataType = 1
   BEGIN
    set @sField = 'UnitPrice'
     declare @result decimal(10, 6)
   END
ELSE IF @dataType = 2
   BEGIN
     set @sField = 'Quantity'
     declare @result int
   END

SELECT @paramlist = '@orderID int,
               @productID int'

set @orderID = 10248
set @productID = 42

set @sql = 'SELECT @result = SUM([' + @sField + ']) FROM [Order Details]
WHERE orderID = @orderID AND productID=@productID'

exec sp_executesql @sql, @paramlist

12 Nov 2005 3:55 AM

Alejandro Mesa

Scott,

This is not a good practice, but if you insist then try using sql_variant
datatype.

declare @result sql_variant

....

exec sp_executesql @sql, N'@orderID int, @result sql_variant output',
@orderID,
@result output
print @result
go

AMB

Show quote

"Scott" wrote:

> In FIGURE 1, my code works fine, allowing dynamic fields to be passed. My
> attempt in FIGURE 2 was to have 2 WHERE parameters and an IF test that
> allows changing the @field and @result variables because different fields
> will need different data types for a @result variable. I'm failing bad as
> this is my first dealings using the sp_executesql sproc.
>
> Can anyone help me structure FIGURE 2? The code runs on Northwind.
>
> FIGURE 1:
> declare @sql nvarchar(4000)
> declare @orderID int, @sField sysname
> declare @result int
>
> set @orderID = 10248
> set @sField = 'UnitPrice'
>
> set @sql = 'SELECT @result = SUM([' + @sField + ']) FROM [Order Details]
> WHERE orderID = @orderID'
>
> exec sp_executesql @sql, N'@orderID int, @result int output', @orderID,
> @result output
> print @result
>
>
> FIGURE 2:
>
> declare @sql nvarchar(4000), @paramlist nvarchar(4000)
> declare @sField sysname
> declare @dataType int
> declare @orderID int, @productID int
>
> set @dataType = 1
>
> IF @dataType = 1
>    BEGIN
>     set @sField = 'UnitPrice'
>      declare @result decimal(10, 6)
>    END
> ELSE IF @dataType = 2
>    BEGIN
>      set @sField = 'Quantity'
>      declare @result int
>    END
>
> SELECT @paramlist = '@orderID int,
>                @productID int'
>
> set @orderID = 10248
> set @productID = 42
>
> set @sql = 'SELECT @result = SUM([' + @sField + ']) FROM [Order Details]
> WHERE orderID = @orderID AND productID=@productID'
>
> exec sp_executesql @sql, @paramlist
>
>
>

12 Nov 2005 5:29 PM

Scott

please explain why variables are listed twice when using
sp_executesql.

I can't follow the syntax for that line.

Show quote

"Alejandro Mesa" <AlejandroM***@discussions.microsoft.com> wrote in message
news:5D4248A5-01F2-4A75-A71C-98CA52C976E7@microsoft.com...
> Scott,
>
> This is not a good practice, but if you insist then try using sql_variant
> datatype.
>
> declare @result sql_variant
>
> ...
>
> exec sp_executesql @sql, N'@orderID int, @result sql_variant output',
> @orderID,
> @result output
> print @result
> go
>
>
> AMB
>
> "Scott" wrote:
>
>> In FIGURE 1, my code works fine, allowing dynamic fields to be passed. My
>> attempt in FIGURE 2 was to have 2 WHERE parameters and an IF test that
>> allows changing the @field and @result variables because different fields
>> will need different data types for a @result variable. I'm failing bad as
>> this is my first dealings using the sp_executesql sproc.
>>
>> Can anyone help me structure FIGURE 2? The code runs on Northwind.
>>
>> FIGURE 1:
>> declare @sql nvarchar(4000)
>> declare @orderID int, @sField sysname
>> declare @result int
>>
>> set @orderID = 10248
>> set @sField = 'UnitPrice'
>>
>> set @sql = 'SELECT @result = SUM([' + @sField + ']) FROM [Order Details]
>> WHERE orderID = @orderID'
>>
>> exec sp_executesql @sql, N'@orderID int, @result int output', @orderID,
>> @result output
>> print @result
>>
>>
>> FIGURE 2:
>>
>> declare @sql nvarchar(4000), @paramlist nvarchar(4000)
>> declare @sField sysname
>> declare @dataType int
>> declare @orderID int, @productID int
>>
>> set @dataType = 1
>>
>> IF @dataType = 1
>>    BEGIN
>>     set @sField = 'UnitPrice'
>>      declare @result decimal(10, 6)
>>    END
>> ELSE IF @dataType = 2
>>    BEGIN
>>      set @sField = 'Quantity'
>>      declare @result int
>>    END
>>
>> SELECT @paramlist = '@orderID int,
>>                @productID int'
>>
>> set @orderID = 10248
>> set @productID = 42
>>
>> set @sql = 'SELECT @result = SUM([' + @sField + ']) FROM [Order Details]
>> WHERE orderID = @orderID AND productID=@productID'
>>
>> exec sp_executesql @sql, @paramlist
>>
>>
>>

12 Nov 2005 10:44 PM

Alejandro Mesa

Scott,

They are not listed twice, two diferent contexts using same name. The second
parameter of sp_executesql is the list that define all variables being used
in the script that it will execute and the third is the list of values for
those parameters. If you prefer diff names then you can write it as:

declare @sql nvarchar(4000)
declare @orderID int, @sField sysname
declare @result sql_variant

set @orderID = 10248
set @sField = 'UnitPrice'

set @sql = 'SELECT @r = SUM([' + @sField + ']) FROM [Order Details]
WHERE orderID = @i'

exec sp_executesql @sql, N'@i int, @r sql_variant output', @orderID,
@result output
print @result
go

AMB

Show quote

"Scott" wrote:

> please explain why variables are listed twice when using
> sp_executesql.
>
>
>
> I can't follow the syntax for that line.
>
>
> "Alejandro Mesa" <AlejandroM***@discussions.microsoft.com> wrote in message
> news:5D4248A5-01F2-4A75-A71C-98CA52C976E7@microsoft.com...
> > Scott,
> >
> > This is not a good practice, but if you insist then try using sql_variant
> > datatype.
> >
> > declare @result sql_variant
> >
> > ...
> >
> > exec sp_executesql @sql, N'@orderID int, @result sql_variant output',
> > @orderID,
> > @result output
> > print @result
> > go
> >
> >
> > AMB
> >
> > "Scott" wrote:
> >
> >> In FIGURE 1, my code works fine, allowing dynamic fields to be passed. My
> >> attempt in FIGURE 2 was to have 2 WHERE parameters and an IF test that
> >> allows changing the @field and @result variables because different fields
> >> will need different data types for a @result variable. I'm failing bad as
> >> this is my first dealings using the sp_executesql sproc.
> >>
> >> Can anyone help me structure FIGURE 2? The code runs on Northwind.
> >>
> >> FIGURE 1:
> >> declare @sql nvarchar(4000)
> >> declare @orderID int, @sField sysname
> >> declare @result int
> >>
> >> set @orderID = 10248
> >> set @sField = 'UnitPrice'
> >>
> >> set @sql = 'SELECT @result = SUM([' + @sField + ']) FROM [Order Details]
> >> WHERE orderID = @orderID'
> >>
> >> exec sp_executesql @sql, N'@orderID int, @result int output', @orderID,
> >> @result output
> >> print @result
> >>
> >>
> >> FIGURE 2:
> >>
> >> declare @sql nvarchar(4000), @paramlist nvarchar(4000)
> >> declare @sField sysname
> >> declare @dataType int
> >> declare @orderID int, @productID int
> >>
> >> set @dataType = 1
> >>
> >> IF @dataType = 1
> >>    BEGIN
> >>     set @sField = 'UnitPrice'
> >>      declare @result decimal(10, 6)
> >>    END
> >> ELSE IF @dataType = 2
> >>    BEGIN
> >>      set @sField = 'Quantity'
> >>      declare @result int
> >>    END
> >>
> >> SELECT @paramlist = '@orderID int,
> >>                @productID int'
> >>
> >> set @orderID = 10248
> >> set @productID = 42
> >>
> >> set @sql = 'SELECT @result = SUM([' + @sField + ']) FROM [Order Details]
> >> WHERE orderID = @orderID AND productID=@productID'
> >>
> >> exec sp_executesql @sql, @paramlist
> >>
> >>
> >>
>
>
>

12 Nov 2005 10:59 PM

Erland Sommarskog

Scott (sbai***@mileslumber.com) writes:

> please explain why variables are listed twice when using
> sp_executesql.
>
>
>
> I can't follow the syntax for that line.

Is this syntax you are wondering over:

>> exec sp_executesql @sql, N'@orderID int, @result sql_variant output',
>> @orderID,
>> @result output

The first parameter to sp_executesql is the SQL statement. The second
is the parameter-list definition. The remaining parameters are the
actual parameters passed to the SQL statement according to the parameter
list. Saying only:

exec sp_executesql @sql, N'@orderID int, @result sql_variant output

Yields an error about missing parameters.

By adding OUTPUT to a parameter, you can get the output value from the
parameter, provided that the parameter is OUTPUT in the parameter
declaration as well.

--
Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

12 Nov 2005 9:44 AM

Erland Sommarskog

Scott (sbai***@mileslumber.com) writes:
Show quote

> In FIGURE 1, my code works fine, allowing dynamic fields to be passed. My
> attempt in FIGURE 2 was to have 2 WHERE parameters and an IF test that
> allows changing the @field and @result variables because different fields
> will need different data types for a @result variable. I'm failing bad as
> this is my first dealings using the sp_executesql sproc.
>...
> IF @dataType = 1
>    BEGIN
>     set @sField = 'UnitPrice'
>      declare @result decimal(10, 6)
>    END
> ELSE IF @dataType = 2
>    BEGIN
>      set @sField = 'Quantity'
>      declare @result int
>    END

You cannot declare the same variable twice. As Alexander says, you could
use the sql_variant data type, but this is sort of meaning less exercise.
This is not how you program T-SQL in practice.

--
Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

12 Nov 2005 5:27 PM

Scott

Is there any way to have a condition where a variable is declared a certain
type if one test is true and then another type if condition 2 is true?

Show quote

"Erland Sommarskog" <esq***@sommarskog.se> wrote in message
news:Xns970C6CE3B7D29Yazorman@127.0.0.1...
> Scott (sbai***@mileslumber.com) writes:
>> In FIGURE 1, my code works fine, allowing dynamic fields to be passed. My
>> attempt in FIGURE 2 was to have 2 WHERE parameters and an IF test that
>> allows changing the @field and @result variables because different fields
>> will need different data types for a @result variable. I'm failing bad as
>> this is my first dealings using the sp_executesql sproc.
>>...
>> IF @dataType = 1
>>    BEGIN
>>     set @sField = 'UnitPrice'
>>      declare @result decimal(10, 6)
>>    END
>> ELSE IF @dataType = 2
>>    BEGIN
>>      set @sField = 'Quantity'
>>      declare @result int
>>    END
>
> You cannot declare the same variable twice. As Alexander says, you could
> use the sql_variant data type, but this is sort of meaning less exercise.
> This is not how you program T-SQL in practice.
>
> --
> Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se
>
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

12 Nov 2005 10:55 PM

Erland Sommarskog

Scott (sbai***@mileslumber.com) writes:

> Is there any way to have a condition where a variable is declared a
> certain type if one test is true and then another type if condition 2 is
> true?

No. Typing in T-SQL is static.

--
Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

12 Nov 2005 10:53 PM

Alejandro Mesa

Erland,

I did not say to declare the variable twice. what I said was to use
sql_variant as the datatype.

AMB

Show quote

"Erland Sommarskog" wrote:

> Scott (sbai***@mileslumber.com) writes:
> > In FIGURE 1, my code works fine, allowing dynamic fields to be passed. My
> > attempt in FIGURE 2 was to have 2 WHERE parameters and an IF test that
> > allows changing the @field and @result variables because different fields
> > will need different data types for a @result variable. I'm failing bad as
> > this is my first dealings using the sp_executesql sproc.
> >...
> > IF @dataType = 1
> >    BEGIN
> >     set @sField = 'UnitPrice'
> >      declare @result decimal(10, 6)
> >    END
> > ELSE IF @dataType = 2
> >    BEGIN
> >      set @sField = 'Quantity'
> >      declare @result int
> >    END
>
> You cannot declare the same variable twice. As Alexander says, you could
> use the sql_variant data type, but this is sort of meaning less exercise.
> This is not how you program T-SQL in practice.
>
> --
> Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se
>
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
>