Dynamic SQL headache

[xavstone]

Hello chaps,

Any ideas why this wont work? Apparently varchar and unique identifier cannot be used with the + operator. New to dynamic SQL so havent a scooby...

Thanks in advance.

ALTER PROCEDURE dbo.AddPriceDataToExchangeTable

@symbolId uniqueidentifier,
@date smalldatetime,
@dateId int,
@priceOpen decimal(23,10),
@priceHigh decimal(23,10),
@priceLow decimal(23,10),
@priceClose decimal(23,10),
@volume bigint,
@openInterest int,
@exchange varchar(50)

AS
BEGIN

SET NOCOUNT ON;

Declare @SQL VarChar(1000)

SELECT @SQL = 'INSERT INTO' + @exchange + '(symbolId, dataDate , dateId, priceOpen ,priceHigh ,priceLow ,priceClose, volume, openInterest)'


SELECT @SQL = @SQL + ' VALUES (' + @symbolId + ',' + @date + ',' + @dateId + ',' + @priceOpen + ',' + @priceHigh + ',' + @priceLow + ',' + @priceClose + ',' + @volume + ',' + @openInterest + ')'

Exec (@SQL)


END

 

[gmmastros]

You need to cast (or convert) each variable to a varchar prior to appending it to the @SQL variable. SQL does a decent job of automatically casting data types for you. There are strict rules for this. The rules are explained here:

http://msdn.microsoft.com/en-us/library/ms190309.aspx

SELECT @SQL = 'INSERT INTO' + @exchange + '(symbolId, dataDate , dateId, priceOpen ,priceHigh ,priceLow ,priceClose, volume, openInterest)'


SELECT @SQL = @SQL + ' VALUES (' + [!]Convert(VarChar(10), [/!]@symbolId [!])[/!] + ',' + [!]Convert(VarChar(10), [/!]@date[!])[/!] + ',' +  [!]Convert(VarChar(10), [/!]@dateId[!])[/!] + ',' + [!]Convert(VarChar(10), [/!]@priceOpen[!])[/!] + ',' + [!]Convert(VarChar(10), [/!]@priceHigh[!])[/!] + ',' +  [!]Convert(VarChar(10), [/!]@priceLow[!])[/!] + ',' +  [!]Convert(VarChar(10), [/!]@priceClose[!])[/!] + ',' +  [!]Convert(VarChar(10), [/!]@volume[!])[/!] + ',' + [!]Convert(VarChar(10), [/!]@openInterest[!])[/!] + ')'

Exec (@SQL)

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[r937]

should also leave a space after the keyword INTO and before the @exchange table name

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[markros]

There is no need to convert parameters in this statement at all.

First of all, why you're passing the table name? Do you have several tables with the exact same structure and you want to dynamically insert data in them by passing table's name? If so, here is a re-write of your SP:

ALTER PROCEDURE dbo.AddPriceDataToExchangeTable

@symbolId uniqueidentifier,
@date smalldatetime,
@dateId int,
@priceOpen decimal(23,10),
@priceHigh decimal(23,10),
@priceLow decimal(23,10),
@priceClose decimal(23,10),
@volume bigint,
@openInterest int,
@ExchangeTableName sysname

AS
BEGIN

    SET NOCOUNT ON;

    Declare @SQL nVarChar(1000)

SELECT @SQL = 'INSERT INTO' + quotename(@ExchangeTableName) + '(symbolId, dataDate , dateId, priceOpen ,priceHigh ,priceLow ,priceClose, volume, openInterest)
' VALUES (@symbolId ,@date,@dateId,@priceOpen,@priceHigh,  @priceLow,@priceClose,@volume,@openInterest)

execute sp_ExecuteSQL @SQL, N'@symbolId uniqueidentifier,
@date smalldatetime,
@dateId int,
@priceOpen decimal(23,10),
@priceHigh decimal(23,10),
@priceLow decimal(23,10),
@priceClose decimal(23,10),
@volume bigint,
@openInterest int',

@symbolId,
@date,
@dateId,
@priceOpen,
@priceHigh,
@priceLow,
@priceClose,
@volume,
@openInterest 
    

END

Take a look at this blog post:
Avoid Conversions In Execution Plans By Using sp_executesql Instead of Exec

PluralSight Learning Library

 

[r937]

SELECT @SQL = 'INSERT INTO' + quotename(@ExchangeTableName)

you, too, need a space after INTO

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[markros]

Correct. And also close the string. Ok, another correction:

ALTER PROCEDURE dbo.AddPriceDataToExchangeTable

@symbolId uniqueidentifier,
@date smalldatetime,
@dateId int,
@priceOpen decimal(23,10),
@priceHigh decimal(23,10),
@priceLow decimal(23,10),
@priceClose decimal(23,10),
@volume bigint,
@openInterest int,
@ExchangeTableName sysname

AS
BEGIN

    SET NOCOUNT ON;

    Declare @SQL nVarChar(1000)

SELECT @SQL = 'INSERT INTO ' + quotename(@ExchangeTableName) + '(symbolId, dataDate , dateId, priceOpen ,priceHigh ,priceLow ,priceClose, volume, openInterest)
' VALUES (@symbolId ,@date,@dateId,@priceOpen,@priceHigh,  @priceLow,@priceClose,@volume,@openInterest)'

execute sp_ExecuteSQL @SQL, N'@symbolId uniqueidentifier,
@date smalldatetime,
@dateId int,
@priceOpen decimal(23,10),
@priceHigh decimal(23,10),
@priceLow decimal(23,10),
@priceClose decimal(23,10),
@volume bigint,
@openInterest int',

@symbolId,
@date,
@dateId,
@priceOpen,
@priceHigh,
@priceLow,
@priceClose,
@volume,
@openInterest 
    

END

PluralSight Learning Library

 

[gmmastros]

Just to clarify the reason I responded the way I did...

SQL Server uses data type precedence to convert data from one type to another. Sure... this example was with dynamic SQL, but the problem would have been the same if the OP had wanted to return multiple columns concatenated instead ob building a dynamic sql string.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[markros]

BTW, there should not be ' before values in my version. Wish tek-tips had update feature so I can fix typos in my posts.

PluralSight Learning Library

 

[simian336]

IMHO while debugging dynamic sql you should always

Print @sql

Prior to executeing it.

Simi

 

[xavstone]

Wow, thanks for all the tips guys! Esp Markros for actually rewriting my rubbish SQL! :-D

Sorry for late reply been on holiday.

Thanks again.

Xavi