We help IT Professionals succeed at work.

SQL check if parameter is empty

zintech
zintech used Ask the Experts™
on
I have a SQL statement that I have verified is correct.  If the value of the only parameter on the query is empty, then I want the query to show.  I haven't got it to work thus far.  I have tried

IF (@param = '')
BEGIN
-execute SQL
END

Also tried

IF (ISNULL(@param))
BEGIN
-execute SQL
END

Also tried

IF (@param IS NULL)
BEGIN
-execute SQL
END

Nothing works.  It won't execute the SQL statement and I can't figure out why.  The value of @param comes form a TextBox on the page that doesn't have anything in it.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Try

IF (LEN(@param) = 0)
BEGIN
-execute SQL
END
Hamed NasrRetired IT Professional

Commented:
Check the text box value.
Try to type a character in the text box then erase.
what datatype is @param and what is the definition of empty? by which way do you pass in the data, is this a stored procedure where the parameter is type-checked?
what way does the user pass in the data?
nishant joshiTechnology Development Consultant

Commented:
IF (ISNULL(@param,'') = '')
BEGIN
--------------
END

Open in new window

that's just wasting cpu cycles

Commented:
Print something above or inside a loop so you have an idea its either going inside the loop or there is something wrong with the query

Print( 'Loop Start')

IF ((ISNULL(@param,'') = '') or (LEN(@param) = 0))

BEGIN
Print('Inside Loop')
-execute SQL


END
Systems Development / Support Specialist
Commented:
-- Usage [uspGetData] ''
ALTER PROCEDURE [dbo].[uspGetData] 
	@ProductId int
AS
BEGIN
	SET NOCOUNT ON;
	Print( 'Loop Start')
	IF ((ISNULL(@ProductId,'') = ''))
		BEGIN
			Print('Inside Loop')
			--SELECT* from tblProduct where ProductId = @ProductId
		END
END

Open in new window