how to insert deafult values dynamically for null fields

Question

Post reply

how to insert deafult values dynamically for null fields

waleed_m_M

Say Hey Kid

Points: 695
More actions
June 15, 2009 at 11:14 am

#138611

hi
I want a sql query for a stored procedure that take 2 input paramters (image,and varbinary) and tests each of them to be null or not,
in case of being null I want to insert Default value for the column in DB table.
here's the problem ,I'll explain them in points in brief:
1-I'm working on a dynamic sql query generator that check table schema at runtime, (so I don't know how many columns in advance in the selected table or their types,or thier default values ,only at Runtime this inormation is fectched)
2-then sql queries are produced in SQL script file (.sql)
3-when called generated code ,not all fields are null ,some may be and some may not be.
I have tried some scripts but the produced code has some problem in varbinary and Image types , how to put the generated queryr into text file (.sql)
I hope I made a clear description of problem
thanks

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Florian Reischl SSC-Dedicated Points: 37301 More actions · Answer 1

Something like this?

Script all data of a table[/url]

Flo

waleed_m_M Say Hey Kid Points: 695 More actions · Answer 2

thanks alot

I shall study that and test it,any way my current code is

----------------------------

CREATE PROCEDURE [dbo].[sp_Table_1_Insert_WithDefaultValues]

@c_varBin varbinary (50) = null ,

@c_varBin_max image = null ,

@c_smallInt smallint = null ,

@CXml text = null ,

@CImage image = null ,

@CMoney float = null ,

@c_tinyInt tinyint = null ,

@CVarchar varchar (50) = null ,

@CVarcharMax text = null ,

@c_nvarChar varchar (50) = null ,

@CNvarcharMax text = null ,

@c_dateTime datetime = null ,

@errorcode int OUTPUT

AS

SET NOCOUNT ON

-- INSERT a new row in the table

INSERT INTO [dbo].[Table_1]( [c_varBin],[c_varBin_max],[c_smallInt],[c_xml],[c_image],[c_money],[c_tinyInt],[c_varchar],[c_varchar_max],[c_nvarChar],[c_nvarchar_max],[c_dateTime] )

VALUES ( isnull(convert(varchar(max),@c_varBin),'Default') ,isnull(convert(varchar(max),@c_varBin_max),'Default') ,isnull(convert(varchar(max),@c_smallInt),'Default') ,isnull(convert(varchar(max),@CXml),'Default') ,isnull(convert(image,@CImage),'DEFAULT') ,isnull(convert(varchar(max),@CMoney),'Default') ,isnull(convert(varchar(max),@c_tinyInt),'Default') ,isnull(convert(varchar(max),@CVarchar),'Default') ,isnull(convert(varchar(max),@CVarcharMax),'Default') ,isnull(convert(varchar(max),@c_nvarChar),'Default') ,isnull(convert(varchar(max),@CNvarcharMax),'Default') ,isnull(convert(varchar(max),@c_dateTime),'Default') ) ;

-- Get the Error Code for the statment just executed

SET @errorcode = @@ERROR

GO

------------------------------------

the lines of image ,and varbinary insertion make a problem

Florian Reischl SSC-Dedicated Points: 37301 More actions · Answer 3

Have a look to my script. You have to insert them like this:

INSERT INTO AnyTable (VarBinaryColumn)

SELECT 0x12345

Flo

waleed_m_M Say Hey Kid Points: 695 More actions · Answer 4

hi

I'll add extra script to get default value for each column first if the equivalent passed parameter is null ,something like that:

-----------------------

create table #temp

(

column_name sysname,

column_def nvarchar(4000)

)

insert into #temp SELECT column_name,column_default

FROM INFORMATION_SCHEMA.COLUMNS

where table_Name ='table_1'

declare @t nvarchar(4000)

set @t=(select column_def from #temp where column_name='')

select @t -- just for visualization

if @v-2 =null --@v here is the passed parameter for stored procedure

begin

set @v-2 = convert ( ,@t)

end

select @v-2 -- just for visualization

drop table #temp

---------------------------------

it works for most cases (even in image) ,but when is varbinary I notice some strange thing

when @t is 0x0343 ,@v has different value 0x28 !!!!

why is this mismatch in converted value in varbinary?

thanks

Florian Reischl SSC-Dedicated Points: 37301 More actions · Answer 5

You cannot use CONVERT to convert VARBINARY into a hexadecimal string.

Either use this function I also published here (as mentioned in the other script 😉 ):

http://www.sqlservercentral.com/scripts/Hex/65997/

Or, for better performance, use the tally table version I posted here:

http://www.sqlservercentral.com/Forums/Topic695508-338-17.aspx

If you don't know what a tally table is search this site. You will find an article by Jeff Moden.

Flo

waleed_m_M Say Hey Kid Points: 695 More actions · Answer 6

thanks

but I don't want to convert Varbinary To String ,I want to convert nVarchar to varbinary (the inverse conversion) 🙂

Florian Reischl SSC-Dedicated Points: 37301 More actions · Answer 7

So try this:

DECLARE @Txt VARCHAR(10)

DECLARE @Bin VARBINARY(6)

SELECT @Txt = '0102030405'

SELECT @Bin = 0x0

UPDATE t SET

@Bin = @Bin + CONVERT(BINARY(1), CONVERT(TINYINT, SUBSTRING(@Txt, (N * 2) - 1, 2)))

FROM Tally t

WHERE N <= LEN(@Txt) / 2

SELECT @Bin

Flo