December 12, 2012 at 8:38 am
EXEC SP_EXECUTESQL @SQL,
@Parameters,
@lColumnValue
is there a way to make the third col in this call to be truly dynamic. My @Parameters is a generated string however it appears in all the examples i've seen the third col in the exectute sql call is hard coded.
Is there a way to make this dynamic?
December 12, 2012 at 8:54 am
here's an example of what i'm trying to do. I'm getting an "Error converting data type nvarchar to int." error. i want the script to be smart enough so that it only includes @param1 and or @param2 is they're not null. is this possible?
DECLARE @param1 INT = NULL,
@param2 INT = NULL,
@select NVARCHAR(1000) = '',
@params NVARCHAR(200),
@paramValues NVARCHAR(1000)
SET @param1 = 1
-- SET @param2 = 1
SET @select = 'SELECT TOP 1 * FROM sys.columns (NOLOCK)
where ' + CASE
WHEN @param1 IS NOT NULL THEN 'column_id = @param1'
ELSE ''
END + CASE
WHEN @param2 IS NOT NULL THEN ' or column_id = @param2'
ELSE ''
END
SET @params= CASE
WHEN @param1 IS NOT NULL THEN '@param1 int '
ELSE ''
END + CASE
WHEN @param2 IS NOT NULL THEN ' , @param2 int'
ELSE ''
END
SET @paramValues = CASE
WHEN @param1 IS NOT NULL THEN '@param1 = ' + CAST(@param1 AS VARCHAR)
ELSE ''
END + CASE
WHEN @param2 IS NOT NULL THEN ', @param2 = ' + CAST(@param2 AS VARCHAR)
ELSE ''
END
PRINT @select --SELECT TOP 1 * FROM sys.columns (NOLOCK) where column_id = @param1
PRINT ''
PRINT @params --@param1 int
PRINT ''
PRINT @paramValues --@param1 = 1
EXEC Sp_executesql
@select,
@paramValues
December 12, 2012 at 9:07 am
You can modify the SQL Statement so it will get 2 parameters each time, but won't always use both of them. Here is a small example that is based on your example:
DECLARE @param1 INT,
@param2 INT ,
@select NVARCHAR(1000),
@params NVARCHAR(200),
@paramValues NVARCHAR(1000)
SET @param1 = 1
SET @param2 = 2
SET @select = 'SELECT * FROM sys.columns (NOLOCK)
where ' + CASE
WHEN @param1 IS NOT NULL THEN 'column_id = @param1'
ELSE '1=2'
END + CASE
WHEN @param2 IS NOT NULL THEN ' or column_id = @param2'
ELSE ' or 1=2'
END
SET @params= '@param1 int, @param2 int '
EXEC sp_executesql
@select,
@param1, @param2
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
December 12, 2012 at 9:15 am
Yes. You just build your parameters string dynamically.
declare @params nvarchar(max) = '';
if @Param1 is not null
set @params += ', @Param1 int';
if @Param2 is not null
set @params += ', @Param2 int';
set @params = stuff(@Params, 1, 1, ''); -- to get rid of any leading comma
Then, when you call sp_executeSQL, put @params where you put the parameters list.
- 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
December 12, 2012 at 12:46 pm
thanks guys
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply