March 9, 2008 at 11:40 pm
Hey all. Thanks in advance for any help at all.
I'm having problems declaring and using variables inside dynamic SQL. The variables don't appear to be saving the values assigned to them throughout the entire execution of the dynamic code.
Here is what I have:
DECLARE @script nvarchar(max)
SET @script = '
DECLARE @x float
DECLARE @y float
SELECT@x = x, @y = y
FROM' + @data + '
WHEREoid = ' + CAST(@oid AS nvarchar) + '
ANDtimestamp = ''' + CAST(@timestamp AS nvarchar) + '''
DELETE FROM' + @data + '
WHEREoid = ' + CAST(@oid AS nvarchar) + '
ANDtimestamp = ''' + CAST(@timestamp AS nvarchar) + '''
INSERT INTO' + @data + '_Error(oid, timestamp, x, y)
VALUES(' + CAST(@oid AS nvarchar) + ', ''' + CAST(@timestamp AS nvarchar) + ''', @x, @y)
'
EXEC sp_executesql @script
The table named "@data + '_Error'" cannot take NULL values into its "x" and "y" columns. When this is executed, I get an error saying that it is trying to insert NULL values into these columns. I've checked the select statement that comes before it and there does exist a row that will return for it with proper "x" and "y" values.
So I'm thinking that it's not saving the values into "@x" and "@y" for some reason. And it knows that those variables exist since it doesn't give me any undeclared variable errors.
Am I doing anything wrong? Is this not possible in dynamic SQL? How should I change it?
Thanks, again, in advance for any help.
March 10, 2008 at 5:27 am
Not knowing what your data looks like, you are testing for an exact match on timestamp, but not testing for the existence of rows at that timestamp beforehand. Thus you could have nulls in x and y because there is not a row at the timestamp you selected. I used a basic example of a row created 1/1/2008 and your dynamic sql worked fine as is. I would suggest another block surrounding the code you provided. Test for the existence of rows at the timestamp and the table, and if rows are found execute the second block.
USE [SQLHelp]
GO
/****** Object: Table [dbo].[TestTable] Script Date: 03/10/2008 06:19:13 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[TestTable](
[oid] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[timestamp] [datetime] NULL,
[x] [float] NULL,
[y] [float] NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
USE [SQLHelp]
GO
/****** Object: Table [dbo].[TestTable_Error] Script Date: 03/10/2008 06:20:15 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[TestTable_Error](
[oid] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[timestamp] [datetime] NULL,
[x] [float] NULL,
[y] [float] NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
Insert into testtable
values
(1,'01/01/2008',1,1)
DECLARE @timestamp DATETIME
SET @timestamp = '01/01/2008'
DECLARE @data VARCHAR(50)
SET @data = 'TestTable'
DECLARE @oid VARCHAR(50)
SET @oid = '1'
DECLARE @script nvarchar(max)
SET @script = '
DECLARE @x float
DECLARE @y float
SELECT @x = x, @y = y
FROM ' + @data + '
WHERE oid = ' + CAST(@oid AS nvarchar) + '
AND timestamp = ''' + CAST(@timestamp AS nvarchar) + '''
DELETE FROM ' + @data + '
WHERE oid = ' + CAST(@oid AS nvarchar) + '
AND timestamp = ''' + CAST(@timestamp AS nvarchar) + '''
INSERT INTO ' + @data + '_Error(oid, timestamp, x, y)
VALUES (' + CAST(@oid AS nvarchar) + ', ''' + CAST(@timestamp AS nvarchar) + ''', @x, @y)
'
EXEC sp_executesql @script
GO
select * from testtable_error
March 10, 2008 at 6:19 am
You could avoid the use of those pesky variables altogether if you want...
DECLARE @script nvarchar(max)
SET @script = '
INSERT INTO ' + @data + '_Error(oid, timestamp, x, y)
SELECT ' + CAST(@oid AS nvarchar) + ', ''' + CAST(@timestamp AS nvarchar) + ''', x, y
FROM ' + @data + '
WHERE oid = ' + CAST(@oid AS nvarchar) + '
AND timestamp = ''' + CAST(@timestamp AS nvarchar) + '''
DELETE FROM ' + @data + '
WHERE oid = ' + CAST(@oid AS nvarchar) + '
AND timestamp = ''' + CAST(@timestamp AS nvarchar) + '''
'
PRINT @script
EXEC sp_executesql @script
cheers
ChrisM
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
March 11, 2008 at 11:11 am
The reason for the nulls is probably that you don't have any matching rows for your Where clause.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
March 11, 2008 at 1:54 pm
Yep. You guys are right. There wasn't a matching row in the table.
I missed this because the timestamp was truncated when it was casted as NVARCHAR. In other words, the @timestamp parameter would have found a row, but not after being truncated by the cast.
I ended up taking advantage of the parameters for sp_executesql to pass the actual value of @timestamp.
Thanks for confirming that variables can indeed be used in this way (this is mainly where I was getting worried) and getting me to look at the table values again.
September 21, 2011 at 5:52 am
Hi I have a requirement where i need to take the database default collation for creating the view.
There is an option called database_default but this query is not working as expected
No.1)
select col1 collate database_default from table1
union
select col1 from table2
Here col1 collation is dynamic based on the schema installation
Above query works fine onlyi f i run it as it is.Suppose if i run the same query in another way by creating the view
create view v1 as select col1 collate database_default from table1
then do a union operation
No.2 )
select col1 from v1
union
select col1 from table2
then this is not working. How to make this working.
Is there any otherway where can i pass the collation value as variable
something like declare @col
set @col=select collation_name from sys.columns where table_name='table1'
and column_name='col1'
and then
create view v1 as select col1 collate @col from table1
such that my union query should work (no.2)
please help me out
September 21, 2011 at 12:57 pm
Did you mean to post in an old, unrelated thread, rather than make a new thread about your question ?
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply