December 9, 2010 at 8:47 am
This is a parameter passed in....
DECLARE @D_production_date_time SQL_VARIANT
----
DECLARE @machine_flip VARCHAR(12)
DECLARE @production_time DATETIME
DECLARE @dbStr VARCHAR(15)
SET @D_production_date_time = '2010-11-16 10:15:12.493'
SET @production_date_time = @D_production_date_time
SET @machine_flip = '840'
SET @machine_flip =
CASE
WHEN RIGHT(SUBSTRING(@machine_flip ,4, 1),4) = '' THEN
'''ARP0'+ @machine_flip +''''
ELSE
''' ARP'+ @machine_flip +''''
END
SET @dbStr = 'ARPDBS'
DECLARE @tsql nvarchar (2000)
SET @tsql =
'IF EXISTS
(
SELECT *
FROM ' + @dbStr + '
WHERE prod_Date_time = ''' + CAST(@production_date_time AS VARCHAR(50))+''' --This works
AND machine = '+ @machine_flip +'
)
BEGIN
--This isn't working and I tried severeal cast and converts
SET ''' + @production_date_time +''' = DATEADD(SECOND,3, ''' + @production_date_time +''' )
END
END'
EXEC (@tsql)
December 9, 2010 at 8:53 am
At first glance I would ask and say have all the quotes been closed correctly.
December 9, 2010 at 8:56 am
Yes they have
December 13, 2010 at 10:44 am
It generally helps when you include the error messages you are getting for a given input...
Put this in front of EXEC (@tsql):
print @tsql;
Inspect what it prints and try to run that statement. Correct that statement so it works correctly, then track back thru your code to figure out what to change to create the newly corrected statement.
December 21, 2010 at 4:30 am
What are you trying to set with the last statement?
_____________
Code for TallyGenerator
December 21, 2010 at 8:00 am
I'm seeing two problems:
SET ''' + @production_date_time +''' = DATEADD(SECOND,3, ''' + @production_date_time +''' )
First, assuming that @production_date_time = '20101221' (today's date), if this succeeded you would end up with:
SET '12/21/2010' = DateAdd(SECOND,3,'12/21/2010')
You can't add a datetime variable to a string this way.
Second, if you're trying to set the variable, it needs to be declared within the dynamic sql.
I think what you need is:
DECLARE @tsql nvarchar (2000)
SET @tsql = 'DECLARE @production_date_time;
SET @production_date_time = ''' + convert(char(8), @production_date_time) + ''';
IF EXISTS
(
SELECT *
FROM ' + @dbStr + '
WHERE prod_Date_time = ''' + CAST(@production_date_time AS VARCHAR(50))+''' --This works
AND machine = '+ @machine_flip +'
)
BEGIN
--This isn't working and I tried severeal cast and converts
SET @production_date_time = DATEADD(SECOND,3, @production_date_time)
END
END'
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
December 30, 2010 at 8:22 pm
Instead of exec(@tsql) please use sp_executesql with values passed as parameters - do not concatenate values!
December 30, 2010 at 9:02 pm
Whenever dealing with dynamic query, its good to first use PRINT command, execute the string captured by print command to check the desired result. If you are satisfied with the result, then comment the PRINT and use sp_ExecuteSQL command with parameters.
PRINT command limitation - a message string can be up to 8,000 characters long if it is a non-Unicode string, and 4,000 characters long if it is a Unicode string. Longer strings are truncated.
So, if you are expecting string more than allowed limit then you can use (temp) table to insert the generated string and follow the same steps to get the result.
Thanks
December 31, 2010 at 10:56 am
Now I have more time to write some details and example. Here it is:
-- Works from sql2000 up
declare @sqlWithoutConcatenation nvarchar(4000)
-- We used parameter @id two times, and @date one time.
-- There is no "+" operator. "+" is bad!
set @sqlWithoutConcatenation = '
select id, name, crdate
from sysobjects
where id between @id and 10000*@id
and crdate > @date'
declare @someDate datetime
set @someDate = getdate() - 10000
exec sp_executesql @sqlWithoutConcatenation,
N'@id int, @date datetime', -- all parameters declared within one string
10, @someDate -- parameter values are separated! Native types - no conversion to varchar!
There are no values concatenated with "+" in our sql command. We are using parameters (@), so sql server can reuse cached plan immediately, it doesn't have to compile it and search for best execution path.
Please, execute this query and you will see what statements are in your cache (works from sql2000 up):
-- Cached and compiled execution plans
select sql, usecounts from syscacheobjects
where cacheobjtype='Compiled Plan'
and dbid=db_id() -- id of the database you are interested in
order by sql
If you see many same queries that are different just by some constant value, you have a bad unparametirezid query somewhere in your code!
Cache is limited. So, new executions plans push out old ones (oldest by last time used).
If you do not use parametrized query, and just do a concatenation, sql has to rethink every time how to execute it (what execution plan will be) and cache of plans will be quickly flooded with your bad query. That will push all other compiled plans out of the cache, and your server can experience performance problems.
There are 3 rules for dynamic sql:
1) values that change from execution to execution you should NOT concatenate with "+". Just replace them with parameters. (NO concatenation!)
2) values that are the same (constant) from execution to execution you should type directly into sql string (NO concatenation and no parametrization!)
3) object names (table name, column name, function name) that you change from execution to execution are the only thing you have to concatenate - they cannot be replaced by parameters.
Beside performance, there are few more benefits of using parameters in your dynamic sql:
- there is no complicated conversions to varchar, that sometimes cause other problems and errors (not using index, errors because of language-dependant different date formats etc )
- such code is safe from sql-injection attacks
- it is simpler to read, because there is no "+" operators and quoting in sql statement
December 31, 2010 at 11:44 pm
{edit} Didn't read down far enough and withdrew this post.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply