January 17, 2013 at 9:09 am
Background: To add materials to our DB we often get xls files from users. I have the xls formatted so I can run a sproc that dynamically creates a script. This way I can run the generated script across various datasets. It works well and can be automated. Yeah me.
Issue: We have now introduced nulls. I have to allow these nulls. Please don't tell me my data design is wrong. 😀
Here is a snippet of my code:
DECLARE @MaterialsID [nvarchar](50)
,@MaterialName [nvarchar](150),@Width2 varchar(255), @Q char(1),@MaterialCategoriesId uniqueidentifier,@SQL NVarchar(Max)
SET @Q = ''''
SET @MaterialCategoriesId = 'BB2F00EF-B434-47EB-ADA6-271EE8696A4D'
SET @MaterialName = 'blah'
SET @MaterialsId = 'BB2F00EF-B434-47EB-ADA6-271EE8696A4E'
SET @Width2 = null
SET @SQL='
INSERT INTO [dbo].[Foo]
([MaterialsId],[MaterialCategoriesId],[width])
VALUES
('+@Q+CONVERT(Varchar(50),@MaterialsId)+@Q+'
,'+@Q+CONVERT(Varchar(50),@MaterialCategoriesId)+@Q+','+@Q+CONVERT(Varchar(50),@Width2)+@Q+''
Print @SQL
That returns an empty print. When I give a value to Width2 it of course works. How can I make this allow the null so that it inserts a null dynamically?
January 17, 2013 at 9:41 am
Try this:
DECLARE @MaterialsID [nvarchar](50),
@MaterialName [nvarchar](150),
@Width2 varchar(255),
@Q char(1),
@MaterialCategoriesId uniqueidentifier,
@SQL NVarchar(Max);
SET @Q = '''';
SET @MaterialCategoriesId = 'BB2F00EF-B434-47EB-ADA6-271EE8696A4D';
SET @MaterialName = 'blah' ;
SET @MaterialsId = 'BB2F00EF-B434-47EB-ADA6-271EE8696A4E' ;
SET @Width2 = null;
SET @SQL='
INSERT INTO [dbo].[Foo]
([MaterialsId],[MaterialCategoriesId],[width])
VALUES
(' + @Q + CONVERT(Varchar(50), @MaterialsId) + @Q+ '
,' + @Q + CONVERT(Varchar(50), @MaterialCategoriesId) + @Q + ',' + CONVERT(Varchar(50),ISNULL(@Q + @Width2 + @Q,'null')) + ')';
Print @SQL;
January 17, 2013 at 10:09 am
Your awesome thank you. I thought I broke my scripting solution.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply