June 5, 2011 at 12:43 pm
hi. i have a dinamic query like below:
ALTER PROCEDURE [dbo].[Update_DinamikGal]
@baslik varchar(max)=null,
@resim varchar(max)=null,
@tablo varchar(50),
@sira varchar(10),
@id varchar(10)
AS
BEGIN
declare @sqlcum nvarchar(max)
set @sqlcum ='update '+@tablo+' set baslik=isnull('''+@baslik+''',baslik),resim=isnull('''+@resim+''',resim),sirano=isnull('''+@sira+''',sirano)
where id='''+@id+''' '
exec(@sqlcum)
end
my problem is about isnull. there is an error on about isnull. for example, i don't give a data to @resim parameter. but i enter datas to other fileds. it doesn't save record. if i enter data all of fileds, it saves record.
How can i solve this. Thanks..
June 5, 2011 at 2:04 pm
If any value in a formula is NULL, the result is NULL. This includes concatenation of columns and variables.
(I know not these "fields" of which you speak.)
To prevent NULL results, use ISNULL(), COALESCE() or CASE to substitute values for the NULL.
For example:
Declare @firstName varchar(20)
Declare @lastName varchar(20)
Declare @middlename varchar(20)
-- set values here
Set @firstname = 'John'
Set @lastname = 'Jones'
Select @firstName+' '+@middleName+' '+@lastName as Null_Result
Select isnull(@firstname+' ','')+isnull(@middleName+' ','')+isnull(@lastname,'') as NonNull_Result
In the second select above, the ISNULL() function is used to test the result of adding a space to the variable. If the variable is NULL, the result will be NULL, but the ISNULL() function will substitute a blank to be used in the full character string.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
June 6, 2011 at 3:02 am
Hi
Following my help for you.
----- code ------
declare
@baslik varchar(max),@resim varchar(max),@tablo varchar(50),
@sira varchar(10),
@id varchar(10)
set @tablo = 'emp'
declare @sqlcum nvarchar(max)
set @sqlcum ='update '+@tablo
+' set baslik = '+isnull(@baslik,'''''')+','
+' resim = '+isnull(@resim,'''''')+','
+' sirano = '+isnull(@sira,'''''')+
' where id = '+isnull(@id,'''''')
print @sqlcum
Regards
Siva Kumar J
June 6, 2011 at 7:54 am
You should take a look at this article[/url] about catch all queries on Gail's blog. You might also take special note of the section about sql injection as the procedure you have posted here is wide open to sql injection attack.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 6, 2011 at 9:09 am
Sean is correct, and I was remiss in not mentioning the possibility of SQL Injection attacks. Gail's article, which he refers you to is excellent. Instead of using "EXEC", take a look at sp_ExecuteSQL and "parameterized" dynamic SQL.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
June 6, 2011 at 10:09 am
thanks. i look at that aticle. i hope i solve my problem.
Again thanks.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply