Stored Procedure error

Question

Stored Procedure error

river1

SSC-Insane

Points: 24309
More actions
June 18, 2012 at 4:33 am

#257701

Hi,
I have this stored procedure:
ALTER PROCEDURE USERSGSC (@DB as varchar(50), @RESULTADO INT OUTPUT)
AS
BEGIN
DECLARE @STRSQL AS VARCHAR(5000),
@PARMDEFINITION AS NVARCHAR (4000),
@VALOR INT
SET @STRSQL = 'select @val = SELECT ISNULL(CODIGO,0) FROM '+@DB+'.DBO.UTILIZADORES WHERE NOME = ''sgsc'''
SET @PARMDEFINITION = '@Val INT OUTPUT'
EXECUTE sp_executesql @STRSQL , @PARMDEFINITION, @val = @VALOR OUTPUT
IF @VALOR = 0
BEGIN
SET @STRSQL ='INSERT INTO '+@DB+'dbo.UTILIZADORES values (''SGSC'',
''Utilizador criado para ser usado pelo SGSC''
,3,''1Yzw12ptag341*'',''administrador'',GETDATE()))'
EXEC (@STRSQL)
SET @STRSQL = 'select @val = SELECT ISNULL(CODIGO,0) FROM '+@DB+'.DBO.UTILIZADORES WHERE NOME = ''sgsc'''
SET @PARMDEFINITION = '@Val INT OUTPUT'
EXECUTE sp_executesql @STRSQL , @PARMDEFINITION, @val = @VALOR OUTPUT
RETURN @VALOR
END
ELSE
RETURN @VALOR
END
When I execute it like this:
EXEC USERSGSC 'SGCTCENTRAL',0
I get the following error:
Procedure expects parameter '@statement' of type 'ntext/nchar/nvarchar'.
Can someone help?
Thanks

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

okbangas SSChampion Points: 11773 More actions · Answer 1

The first parameter to sp_executesql must me ntext, nchar or nvarchar, in your case it is varchar. Btw, I do hope you did not actually post an adminstrator password?

Ole Kristian Velstadbråten Bangås - Virinco - Facebook - Twitter

Concatenating Row Values in Transact-SQL[/url]

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

okbangas (6/18/2012)
Btw, I do hope you did not actually post an adminstrator password?

Not to mention storing passwords in plain text...

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 3

okbangas (6/18/2012)
... Btw, I do hope you did not actually post an adminstrator password?

It does look like the real one to me...

So, need an IP address and off you go!

:w00t:

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]

river1 SSC-Insane Points: 24309 More actions · Answer 4

Ok. When I change the type to Nvarchar it worked fine.

Now I have other problem with the same procedure.

Code:

ALTER PROCEDURE sp_SGCT_USERSGSC (@DB as varchar(50))
AS
BEGIN
DECLARE @STRSQL AS NVARCHAR(4000),
@PARMDEFINITION AS NVARCHAR (4000),
@VALOR INT
SET @STRSQL = 'select @val = isnull(CODIGO,0) FROM '+@DB+'.DBO.UTILIZADORES WHERE NOME = ''sgsc'''
SET @PARMDEFINITION = '@Val INT OUTPUT'
EXECUTE sp_executesql @STRSQL , @PARMDEFINITION, @val = @VALOR OUTPUT
IF @VALOR is null
BEGIN
SET @STRSQL ='INSERT INTO '+@DB+'dbo.UTILIZADORES values (''SGSC'',
''Utilizador criado para ser usado pelo SGSC''
,3,''1Yzw12ptag341*'',''administrador'',GETDATE()))'
EXEC (@STRSQL)
SET @STRSQL = 'select @val = CODIGO FROM '+@DB+'.DBO.UTILIZADORES WHERE NOME = ''sgsc'''
SET @PARMDEFINITION = '@Val INT OUTPUT'
EXECUTE sp_executesql @STRSQL , @PARMDEFINITION, @val = @VALOR OUTPUT
RETURN @VALOR
END
ELSE
RETURN @VALOR
END

When I execute it, I receive the following message:

Msg 102, Level 15, State 1, Line 3

Incorrect syntax near ')'.

The 'sp_SGCT_USERSGSC' procedure attempted to return a status of NULL, which is not allowed. A status of 0 will be returned instead.

The procedure should send a value of 0 instead of null given the code isnull(CODIGO,0) but it sends the value 0.

Can you help?

Thanks

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

This chunk has nothing preventing that return parameter being null

IF @VALOR is null

BEGIN

SET @STRSQL ='INSERT INTO '+@DB+'dbo.UTILIZADORES values (''SGSC'',

''Utilizador criado para ser usado pelo SGSC''

,3,''1Yzw12ptag341*'',''administrador'',GETDATE()))'

EXEC (@STRSQL)

SET @STRSQL = 'select @val = CODIGO FROM '+@DB+'.DBO.UTILIZADORES WHERE NOME = ''sgsc'''

SET @PARMDEFINITION = '@Val INT OUTPUT'

EXECUTE sp_executesql @STRSQL , @PARMDEFINITION, @val = @VALOR OUTPUT

RETURN @VALOR

END

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

river1 SSC-Insane Points: 24309 More actions · Answer 6

This is what it suppose to prevent from being null:

SET @STRSQL = 'select @val = isnull(CODIGO,0) FROM '+@DB+'.DBO.UTILIZADORES WHERE NOME = ''sgsc'''

SET @PARMDEFINITION = '@Val INT OUTPUT'

EXECUTE sp_executesql @STRSQL , @PARMDEFINITION, @val = @VALOR OUTPUT

But it does not work...

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 7

If the first select returns no rows then @Valor will be null at the IF statement. There is nothing in the select inside the IF that prevents nulls.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

river1 SSC-Insane Points: 24309 More actions · Answer 8

How can I do it?

Can you suggest how to do it?

Thanks in advance

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 9

How to do what?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

river1 SSC-Insane Points: 24309 More actions · Answer 10

How to make this:

SET @STRSQL = 'select @val = isnull(CODIGO,0) FROM '+@DB+'.DBO.UTILIZADORES WHERE NOME = ''sgsc'''

SET @PARMDEFINITION = '@Val INT OUTPUT'

EXECUTE sp_executesql @STRSQL , @PARMDEFINITION, @val = @VALOR OUTPUT

Pass the value 0 to the variable @valor when there are no NOME equals to SGSC