how to insert default values for varbinary & image

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

how to insert default values for varbinary & image

Author

Topic

waleed_cs2000
Starting Member

14 Posts

Posted - 2009-06-15 : 02:31:42

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.

thanks

waleed_cs2000
Starting Member

14 Posts

Posted - 2009-06-15 : 12:59:26

hi
I'l give more description,there's a small 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

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-06-15 : 13:08:50

>>but the produced code has some problem in varbinary and Image types
What specific problems?

Why don't you post some code to give us an example of the problem you are trying to solve.

Be One with the Optimizer
TG

waleed_cs2000
Starting Member

14 Posts

Posted - 2009-06-15 : 14:57:04

ok ,here the the code

----------------------------
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

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-06-15 : 15:27:47

Ok 1st problem:
in a VALUES clause you can't use expressions (ie: isNull)

2nd problem:
the key word DEFAULT should not be quoted.

3rd problem:
Your parameter datatypes need to match the resulting types of your values. So you don't convert a tinyint value to varchar when you set the @c_tinyint value. The parameter datatype is tinyint. What is the underlying table column datatype?

I assume your table has these defaults defined as part of the column definitions?


use tempdb
create table junk
(i int not null default(-1)
,c_varBin image default(0x01))

--This is OK
insert junk (i, c_varBin) values (default, default)

--This errors out
--insert junk (i, c_varBin) values (isNull(null, default), isNull(null, default))

go

select * from junk
go
drop table junk

Be One with the Optimizer
TG

waleed_cs2000
Starting Member

14 Posts

Posted - 2009-06-16 : 11:21:07

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='<my COLUMN name>')

select @t -- just for visualization

if @v =null --@v here is the passed parameter for stored procedure
begin
set @v = convert ( <MY COLUMN Type>,@t)
end

select @v -- just for visualization
drop table #temp
---------------------------------

it works for most cases (even in image) ,but when <MY COLUMN Type> 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

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-06-16 : 11:42:23

set @v = convert ( <MY COLUMN Type>,@t)

isn't @t your TYPE? (ie: varbinary) Instead of your default varbinary value?

Be One with the Optimizer
TG

waleed_cs2000
Starting Member

14 Posts

Posted - 2009-06-16 : 12:01:44

no
INFORMATION_SCHEMA.COLUMNS.Column_Def is always nvarchar(4000) ,this from msdn documentation .

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-06-16 : 13:03:21

Look at the value of [INFORMATION_SCHEMA].[COLUMNS].[column_default] for your varbinary column. It is the nvarchar value that includes the parentheses. ie: '(0x01)'.

select convert(varbinary(1), '(0x01)')
returns: 0x28

EDIT:
the 0x28 is the left parenthesis

Be One with the Optimizer
TG

waleed_cs2000
Starting Member

14 Posts

Posted - 2009-06-17 : 08:51:04

you are right,I removed parentheses (left and right ), in this code:

-----------------------------------
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='c_varbin')
set @t = replace (@t ,'(','')
set @t = replace (@t ,')','')

select @t

declare @v varbinary
set @v = convert ( varbinary,@t)

select @v
drop table #temp
-----------------------------------

but the result has still some errors when @t = 0x0343 ,then @v =0x03
shall I remove any other thing also?

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-06-17 : 10:27:13

ah ok - I think you will need to use a function to convert a string representation of a varbinary value to an actual varbinary datatype. Sql provides one for the other way around: select sys.fn_varbintohexstr(0x0343)

I believe I wrote one for each direction - I'll look. I'm sure there are some versions here as well. try searching for them.

Be One with the Optimizer
TG

waleed_cs2000
Starting Member

14 Posts

Posted - 2009-06-17 : 11:52:05

thanks TG , I 'll try to know /ask about the other version.

thanks -truly- for being helpful in the previous days, and thanks also for the parenthesis note

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-06-17 : 14:02:35

You're welcome. I found the one I wrote.


if object_id('dbo.fn_HexStrToVarBinary') > 0
	drop function dbo.fn_HexStrToVarBinary
go

create function dbo.fn_HexStrToVarBinary (@s varchar(8000))
returns varbinary(4000)

/*
this function takes a hexidecimal string, converts, and returns it as a varbinary(4000)
sample call:

select	dbo.fn_HexStrToVarBinary(sHex)
from	(
	select	'0x000000002FDAF785' sHex union all
	select '0x0A' union all
	select '0x160A' union all
	select '' union all
	select '0xA'
	union all select replicate('FF', 3999) + 'AA'
	) a

--works for nvarchar(4000) too
select dbo.fn_HexStrToVarBinary(N'' + replicate('FF', 1999) + 'AA')
*/
as
begin
       declare @out varbinary(4000)
              ,@n int
              ,@l int

       set @s = replace(@s, '0x','')
       select @l = len(@s)
              ,@n = 1

       while @n <= @l-1
       begin
	       select	@out = coalesce(@out + b, b)
              from   (
                     select b = convert(binary(1),
			       (convert(varbinary, charindex(substring(@s, @n, 1), '0123456789ABCDEF')-1) * 16)
			       + isNull(nullif(charindex(substring(@s, @n+1, 1), '0123456789ABCDEF')-1,-1),0))
                     ) d
              set @n = @n+2
       end

       return @out
end
go

So that works with your code:


declare @t nvarchar(4000)
       ,@v varbinary(4000)
select @t = N'0x0343'
select @v = dbo.fn_HexStrToVarBinary(@t)
select @v

output:
---------
0x0343

Be One with the Optimizer
TG

waleed_cs2000
Starting Member

14 Posts

Posted - 2009-06-18 : 03:28:08

excellent TG ,you did it very well ,thanks very well and I hope you best wishes.
thanks for help

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources