August 25, 2008 at 12:47 am
Hi, any one can Help my problem.
I have problem to assign the null to the variable. My scenario is I select values and assign variable from table and insert into another temp table for constructing insett script.
My problem is, in selected variables have null value(if db null also) in variable, i could not construct the insert sript. Bcoz i coudnot assign null value to the variable.
select @col1 = isnull(col1,NULL),@col2 = isnull(col2,null) from table
insert into #temp(col1,col2) values (@col1,@col2)
Thanks,
August 25, 2008 at 12:54 am
I'm not sure I understand your question.
Setting a variable to null is as simple as SET @VariableName = NULL
select @col1 = isnull(col1,NULL),@col2 = isnull(col2,null) from table
What are you trying to acheive here? ISNull is a function that returns the first value if the first value is not null and the second value if it is null. With null as the second parameter, that's equivalent to
select @col1 = col1,@col2 = col2 from table
Could you please explain a bit more what you're trying to acheive?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 25, 2008 at 5:05 am
I'm not sure either, but it seems to me that you would like to insert a non-NULL value in the temp table, because a NULL value is not possible (are the fields in your temp table NOT NULL?).
If you try to get a value into your variable, even if the column value is NULL, replace the NULL part of the ISNULL function by an accepted value, e.g. 'NULL' or something like that.
Hope that helps!
Cheers, Bert
[font="Courier New"]
------------------------
MCPD Web Developer, MCPD Windows Developer[/font]
Computers are made to solve problems you did not have in the days they didn't exist.
August 25, 2008 at 5:58 am
Thanks for your reply.
I am dynamicaly constructing insert script.
I don't know whether the column value is null or not.
select @col1 = col1,@co2 = col2 from table
If any one value is null means, i could not get the result. That the reason i put isnull condition in the select statement.
But i want to insert the same value (From select statement) to the other database.
If any column is null, i couldn't get the result. Bcoz I am dynamicaly constructing insert script.
Thanks.
August 25, 2008 at 6:12 am
Hi sugan,
Is your question solved, or do you still need some more explanation? If so, please clarify your question to make it possible for us to give you an answer.
Cheers, Bert
[font="Courier New"]
------------------------
MCPD Web Developer, MCPD Windows Developer[/font]
Computers are made to solve problems you did not have in the days they didn't exist.
August 25, 2008 at 7:36 am
My Problem is not solved...
SET @string = @Declare_column +
" DECLARE @Row_Count integer DECLARE @count integer set @count = 1 DECLARE @string1 varchar(4000)" +
" create table #temp_table("+@col_name+",counter INTEGER IDENTITY(1,1))" +
" INSERT INTO #temp_table("+@sel_name + ")" + @select_statement +
" select @Row_Count = count(*) from #temp_table " +
" WHILE @count <= @Row_Count BEGIN" +
" select " +@Sel_column + " from #temp_table where counter = convert(varchar,@count,101)" +
" SET @string1 = 'insert into " + @tab_name + "("+@sel_name +" ) values ('"+@colmn_values+"')'" +
" print @string1 " +
" set @count = @count + 1 " +
" END " +
" drop table #temp_table "
This is My logic to generate dynamic insert script. In this SP @colmn_values also dynamic. Here Sel_column is column value like @col1= col1, @col2 = col2. i am constructing like this.
then i am substituing @colmn_values for @col1,@col2... . for this also i am constructing.
If this is confused.i will send full code to u.
Is it possible to assign null value to variable. Then how can i pass the null value to the column.
Thanks.
August 25, 2008 at 10:11 am
sugan (8/25/2008)
Is it possible to assign null value to variable.
Yes, and I showed how to do it. As for the second part, try something like this:
SET @string1 = 'insert into ' + @tab_name + '(' +@sel_name + ') values (' + CASE WHEN @colmn_values IS NULL THEN 'NULL' ELSE '''' + @colmn_values + '''' END + ')'
Untested, but it should give you the idea.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 9, 2009 at 3:03 pm
I know this is an older post but....in this case it does appear that the resolution was as simple as "you cant concatinate a null with a string without first handling the null, or it would yield the entire string null".
There is something else i would watch out for though:
If you assign a variable with a select statement where the column you are setting the variable to is null .. the variable value will not be set to null (at least in 2005, thats what i was searching for when i found this)
For instance:
Create Table #Temp(col1 varchar(10))
Declare @col1 varchar(10)
Set @col1 = 'test'
Select @col1 = col1
From #Temp
Select @col1
Drop Table #Temp
Results:
---------------
test
---------------
In this case, even though the select statement would seem to set the value of @col1 to null (since there are no records in the table), it will not.
So if you're building dynamic sql in a loop and are looping through a table or a cursor (ugh) or whatever, make sure to set your variable to or emptystring at the top of each iteration so it doesnt retain previous values.
This was actually a QOtD question but i figured it might be useful in this scenerio.
Cheers!
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply