Solved

T- SQL Check for String.Empty or Nulls in a Passed Parameter

Posted on 2007-11-29
4
21,503 Views
Last Modified: 2012-08-14
I have a stored procedure that receives 20 different parameters as fully built Insert queries...The potential exists though that some of those parameters may be passed to the stored-procedure as empty or nulls.  I need to check to make sure that it is not empty or nulls before I actually EXEC the @SQL.  Can someone please let me know how to do that.  A small portion of the Stored Procedure code is attached.  I have arrows pointing to where I need to check the parameter.  Please show me the right way of doing that.   Help!  msyed1.

 
CREATE PROCEDURE dbo.USP_InsertNewConnectionTables

@CONNInsertSQL varchar(6000) 
 

 

AS
 

BEGIN TRANSACTION

Set Nocount On

Declare @SQL varchar (6000)
 
 

Select @SQL = @CONNInsertSQL

If @SQL <>     String.Empty or NULLS     <<<===================

Begin

  EXEC(@SQL)

  IF @@ERROR <> 0

   BEGIN

    ROLLBACK TRANSACTION

    RETURN (214)

   END

END
 
 
 

COMMIT TRANSACTION

Return 55

GO

Open in new window

0
Comment
Question by:msyed1
  • 2
4 Comments
 
LVL 15

Expert Comment

by:danrosenthal
ID: 20376279
How about:
If @SQL IS NOT NULL
0
 
LVL 25

Accepted Solution

by:
imitchie earned 125 total points
ID: 20376311
check for both '' and NULL

If ISNULL(@SQL, '') <> ''
0
 

Author Comment

by:msyed1
ID: 20376341
imitchie: will this check for both NULLS and an empty string.  I need to check for both....
0
 
LVL 15

Assisted Solution

by:danrosenthal
danrosenthal earned 125 total points
ID: 20376485
Either of these should work:

If @SQL IS NOT NULL OR @SQL <> ''

If ISNULL(@SQL, '') <> ''

0

Featured Post

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Entering a date in Microsoft Access can be tricky. A typo can cause month and day to be shuffled, entering the day only causes an error, as does entering, say, day 31 in June. This article shows how an inputmask supported by code can help the user a…
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now