Very Basic Query Anylizer Question (About Char Strings) 2

[BoulderBum]

I have a function that I want to test:

CREATE FUNCTION fnValidPass( @userN char(20), @pass char(16) )
RETURNS int

AS
BEGIN
declare @retVal int
IF @userN = null
set @retVal = 0
ELSE IF ( SELECT client_password FROM user_info WHERE client_user_name = @userN ) = @pass
set @retVal = 1
ELSE
set @retVal = 0

RETURN @retVal
END



And I tried to test in query anylizer like this:


DECLARE @result INT
DECLARE @name char( 20 )
DECLARE @pass char( 16 )

SET @name = "user1"
SET @pass = "test"

SET @result = dbo.fnValidPass(@name, @pass)

PRINT @result

but I get the following error messages:

Server: Msg 207, Level 16, State 3, Line 6
Invalid column name 'user1'.
Server: Msg 207, Level 16, State 1, Line 7
Invalid column name 'test'.

What is the proper way to pass strings as arguments?

 

[SonOfEmidec1100]

Errors caused by double quotes, use single quotes

SET @name = 'user1'
SET @pass = 'test'

couple of other comments

Use syntax @userN IS NULL not @userN = NULL

I would use varchar, with char all values are padded with spaces - or do you want this ?

 

[BoulderBum]

Thanks tons!

Regarding "IS NULL" vs. "= NULL", is there a functional difference between the two, or is it a matter of convention? Both seem to work.

"I would use varchar, with char all values are padded with spaces - or do you want this ?"

The main reason I used char is because I just started playing with SQL Server a few days ago, and I'm unfamiliar with many of the data types. I assume that varchars keep track of their length with an 8 bit integer (since the length max is 255). Can I further assume that the sacrifice for saving space is slower queries (since length is presumably checked for each varchar value)?

Also, do you know how I would call a stored function from ASP.NET code? I've been having trouble doing so.

I want to do something like:

if( storedFunction() == 1 )

For further detail, go here:

thread855-644633

 

[SonOfEmidec1100]

The time when = NULL does not work is within a CASE or WHERE condition test in a sql statement

Re performance considerations of varchar / char Im sure you won't notice it.

Re ASP.NET Im afraid beyond my limited scope.

Good luck.

 

[BoulderBum]

Thanks again. You've been a big help.

 

[JamesLean]

Actually, the use of "= NULL" is dependent on the ANSI_NULLS setting for the connection, rather than where you are using it.

If ANSI_NULLS is ON, testing for NULL using "= NULL" will never return a match. You need to SET ANSI_NULLS OFF to be able to use this method. Try this example:

DECLARE @p char(20)

SET ANSI_NULLS ON

IF @p = NULL
  PRINT '= NULL - ON'

IF @p IS NULL
  PRINT 'IS NULL - ON'

SET ANSI_NULLS OFF

IF @p = NULL
  PRINT '= NULL - OFF'

IF @p IS NULL
  PRINT 'IS NULL - OFF'

It is better convention to always test using IS NULL to avoid unexpected results.

--James