Sql string not printing when one parameter is null

Question

0.00/5 (No votes)

See more:

Here is my code:

SET @MyParameterEntry = 'My first parameter is:   ' + @P1  + ' My second parameter is:   ' + @P2 + 'My third parameter is:  ' + @P3


Print @MyParameterEntry

When my P3 parameter is NULL, none of my other parameters display - not even my "My first parameter is: " verbiage.

This has not been happening and I am usually able to successfully pass in null or blank parameters and still have my other verbiage print. If I default my P3 parameter to a specific string, the entire @MyParameterEntry variable displays.

What is causing this and how can I resolve it so my entire @MyParameterEntry displays regardless of when my P3 parameter is null?

What I have tried:

Google search, MSDN, compared to other code that works.

Posted 1-Feb-17 11:44am

Member 10379103

Updated 14-Feb-17 4:02am

Add a Solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

RossMW · Answer 1 · 2017-02-01T11:59:00

Solution 1

Add

isnull(@P3,'')

So blank string so passed rather than a null value. Might be safer to do it to all parameters.

Posted 1-Feb-17 11:59am

RossMW

v2

Comments

Karthik_Mahalingam 2-Feb-17 3:27am

5

Member 12999924 · Answer 2 · 2017-02-14T04:02:00

Hi,

Please try this:-
Use Concat in SQL Server
Declare @P1 varchar(10),@P2 varchar(10),@P3 VARCHAR(10),@MyParameterEntry VARCHAR(120)

SET @P1='W'
SET @P2='E'
SET @P3 =NULL

SET @MyParameterEntry = CONCAT( 'My first parameter is: ' , @P1 , ' My second parameter is: ' , @P2 , 'My third parameter is: ' , @P3)

Print @MyParameterEntry