We help IT Professionals succeed at work.

dynamic sql returns null value

jorbroni
jorbroni asked
on
Medium Priority
229 Views
Last Modified: 2012-06-21

I'm trying to exec the code below but it is returning NULL. I am completely stomp on this one. Anybody have any recommendations?


Set @sql = 'Insert into '+db_name()+'..Logging(InsCarrier, Proc_run, Notes, Num, [TimeStamp], D2DataSetID)
      Values ('+@InsCarrier+','+ @Proc + ',''Records Inserted into HealthPlanProvidersTransforumToLoad'','+ @num + ','+ Cast(getdate() as varchar) + ',' + @datasetid+ ')'


EXEC sp_executesql @sql


 
Comment
Watch Question

Shaun KlineLead Software Engineer
CERTIFIED EXPERT

Commented:
What is returning null? The string, the inserted record? The result of the EXEC call?

Author

Commented:

Sorry. The result of the Exec call.
Lead Software Engineer
CERTIFIED EXPERT
Commented:
Are any of your parameters null? The result of a string concatenated with a null is a null. When a null is passed to exec sp_executesql, the result is a null.
HainKurtSr. System Analyst
CERTIFIED EXPERT

Commented:
maybe you should use lots of ' :)

Values ('+@InsCarrier+','+ @Proc + ',...
-->
Values ('''+@InsCarrier+''','''+ @Proc + ''',...

also how did you define @sql?

declare @sql nvarchar(max)

Commented:
try printing the value of the parameters before executing

EXEC sp_executesql @sql

also print the value of @sql before actually executing it.
print @sql

--EXEC sp_executesql @sql
Scott PletcherSenior DBA
CERTIFIED EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019

Commented:
Try these changes:

Values ('+ COALESCE(@InsCarrier, '') +','+ COALESCE(@Proc, '') + ',''Records Inserted into HealthPlanProvidersTransforumToLoad'','+ COALESCE(@num, '') + ','+ Cast(getdate() as varchar) + ',' + COALESCE(@datasetid, '') + ')'

Author

Commented:
You were spot on. On of the parameters was returning NULL. Fix the problem and now it works.

Great info !!!!

Explore More ContentExplore courses, solutions, and other research materials related to this topic.