November 9, 2014 at 4:02 pm
Hi,
I have created dynamic sql to declare variables based on columns from the table and i set values to those variable now here is the issue . i want to check the variable values how do i do that dynamically
drop table test
create table test
(
id varchar(10) not null,
col1 varchar(10) ,
col2 varchar(10)
)
insert into test values(1,'test','')
SELECT *
FROM test
declare @selectquery varchar(max)
declare @columnslist varchar(max)
declare @DeclareVariables varchar(max)
declare @variableslist varchar(max)
declare @id int = 1
declare @sql varchar(max)
--Column list based on table
SELECT @columnslist = COALESCE(@columnslist + ', '+ CHAR(13), '')
+ CAST(COLUMN_NAME AS VARCHAR(100)) + SPACE(1)
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'test'
--declaring variable Variables
SELECT @variablesList = COALESCE(@variablesList + ', '+ CHAR(13), '')
+ '@' + CAST(COLUMN_NAME AS VARCHAR(100)) + SPACE(1)
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'test'
and TABLE_SCHEMA = 'dbo'
select @variablesList,@columnslist
SELECT @DeclareVariables = COALESCE(@DeclareVariables + ', '+ CHAR(13), '')
+ '@' + CAST(COLUMN_NAME AS VARCHAR(100)) + SPACE(1) + DATA_TYPE + CASE DATA_TYPE
WHEN 'Numeric'
THEN '(' + CONVERT(VARCHAR(5), Numeric_Precision) + ',' + CONVERT(VARCHAR(5), Numeric_Scale) + ')'
WHEN 'Decimal'
THEN '(' + CONVERT(VARCHAR(5), Numeric_Precision) + ',' + CONVERT(VARCHAR(5), Numeric_Scale) + ')'
ELSE CASE
WHEN CHARACTER_MAXIMUM_LENGTH IS NOT NULL
THEN '(' + CONVERT(VARCHAR(5), Replace(CHARACTER_MAXIMUM_LENGTH, '-1', 'Max')) + ')'
ELSE ''
END + SPACE(1)
END
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'test'
and TABLE_SCHEMA = 'dbo'
select @DeclareVariables
--assigning values to the variable based on table values
Select @selectquery = 'Declare ' + @DeclareVariables + char(13) + char(13) + 'DECLARE Recordset CURSOR FOR' + Char(13) +
'Select ' + CHAR(13) +
+ @Columnslist + CHAR(13) +
'From dbo.test' + CHAR(13)
print @selectquery
-- Now my next step is verify if the variable is blank or not how do i do that ?
-- How do i verify all of the columns one after the other .
-- I am after the statement like this dynamically
-- IF NOT (@col1 = '') THEN set @SQL = @SQL + '[col1] = ' + @col1 + ' '
--IF NOT (@col2 = '') THEN set @SQL = @SQL + '[col2] = ' + @col2 + ' '
I need to check if the columns are blank or not dynamically as i do not want to hard code the column names there.
Thanks,
Chinna
Its the Journey which gives you Happiness not the Destination-- Dan Millman
November 9, 2014 at 10:14 pm
First a quick question, what are you trying to achieve with this?
😎
You can use the DATALENGTH function on any datatype, here is a quick example:
DECLARE @SQL_STR VARCHAR(MAX) = '
-- Returns 0 if value is null
DECLARE @V1 VARCHAR(10) = NULL;
SELECT ISNULL(DATALENGTH(NULLIF(@V1,''BAD VALUE'')),0)
-- Returns 0 if value is empty
DECLARE @V2 VARCHAR(10) = ''''
SELECT ISNULL(DATALENGTH(NULLIF(@V2,''BAD VALUE'')),0)
-- Returns 0 if value is "Bad"
DECLARE @V3 VARCHAR(10) = ''BAD VALUE''
SELECT ISNULL(DATALENGTH(NULLIF(@V3,''BAD VALUE'')),0)
-- Check a "good value"
DECLARE @V4 VARCHAR(10) = ''GOOD VALUE''
SELECT ISNULL(DATALENGTH(NULLIF(@V4,''BAD VALUE'')),0)
';
EXEC (@SQL_STR)
November 10, 2014 at 3:09 am
IF NOT (@col1 = '') THEN set @SQL = @SQL + '[col1] = ' + @col1 + ' '
Should @SQL go to SET/SELECT list or WHERE clause?
In first case generating
[col1] = CASE @col1 WHEN '' THEN [col1] ELSE @col1 END , ...
may be what you need.
It will work for WHERE clause too, but note the expression will not be sargable.
November 11, 2014 at 2:06 pm
It should go to where clause but how do i get those statements dynamically based on the above table structure without any hard coded script in the code.
Thanks,
Chinna
Its the Journey which gives you Happiness not the Destination-- Dan Millman
November 12, 2014 at 1:34 am
To check your @colx variables values, one needs at least assign that values. I see no source of values in the above code, both generating and generated parts. It generates DECLARE ... and now what?
For example, if that values are known at generation time, them they could be used directly without intermediate @colx. If the values aren't avalable at generation time, then no way to check them certainly.
Can you give us more details on your problem?
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply