How to call SP - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

How to call SP

Author

Topic

Mondeo
Constraint Violating Yak Guru

287 Posts

Posted - 2008-12-08 : 11:17:57

Hi,

I've got an SP and i'm trying to do this

DECLARE @password VARCHAR(8)
SET @password = EXEC sp_GeneratePassword 8

But it says incorrect syntax near EXEC - how do I do it - 8 is a parameter to sp_GeneratePassword (int)

Thanks

hanbingl
Aged Yak Warrior

652 Posts

Posted - 2008-12-08 : 11:29:12

convert your SP to a function and:

select @password=fn_GeneratePassword(8)

Mondeo
Constraint Violating Yak Guru

287 Posts

Posted - 2008-12-08 : 16:24:20

Is there no way to assign the result of an SP to a variable?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-12-08 : 16:26:17

It depends on how you are outputing the information. Are you using an OUTPUT parameter, a RETURN value, or just returning a result set? All 3 will have different ways to write them.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Mondeo
Constraint Violating Yak Guru

287 Posts

Posted - 2008-12-08 : 16:31:23

Result set (i think) This is the other SP


ALTER PROCEDURE [dbo].[sp_GeneratePassword]
(
    @Length int
)

AS

DECLARE @RandomID varchar(32)
DECLARE @counter smallint
DECLARE @RandomNumber float
DECLARE @RandomNumberInt tinyint
DECLARE @CurrentCharacter varchar(1)
DECLARE @ValidCharacters varchar(255)
SET @ValidCharacters = 'ABCDEFGHIJKLMNOPQRSTUVWXYZ'
DECLARE @ValidCharactersLength int
SET @ValidCharactersLength = len(@ValidCharacters)
SET @CurrentCharacter = ''
SET @RandomNumber = 0
SET @RandomNumberInt = 0
SET @RandomID = ''

SET NOCOUNT ON

SET @counter = 1

WHILE @counter < (@Length + 1)

BEGIN

        SET @RandomNumber = Rand()
        SET @RandomNumberInt = Convert(tinyint, ((@ValidCharactersLength - 1) * @RandomNumber + 1))

        SELECT @CurrentCharacter = SUBSTRING(@ValidCharacters, @RandomNumberInt, 1)

        SET @counter = @counter + 1

        SET @RandomID = @RandomID + @CurrentCharacter

END

SELECT @RandomID AS 'Password'

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-12-08 : 16:51:19

You should change it to this:


ALTER PROCEDURE [dbo].[sp_GeneratePassword]
(
    @Length int, @output varchar(32) OUTPUT
)

AS

DECLARE @RandomID varchar(32)
DECLARE @counter smallint
DECLARE @RandomNumber float
DECLARE @RandomNumberInt tinyint
DECLARE @CurrentCharacter varchar(1)
DECLARE @ValidCharacters varchar(255)
SET @ValidCharacters = 'ABCDEFGHIJKLMNOPQRSTUVWXYZ'
DECLARE @ValidCharactersLength int
SET @ValidCharactersLength = len(@ValidCharacters)
SET @CurrentCharacter = ''
SET @RandomNumber = 0
SET @RandomNumberInt = 0
SET @RandomID = ''

SET NOCOUNT ON

SET @counter = 1

WHILE @counter < (@Length + 1)

BEGIN

        SET @RandomNumber = Rand()
        SET @RandomNumberInt = Convert(tinyint, ((@ValidCharactersLength - 1) * @RandomNumber + 1))

        SELECT @CurrentCharacter = SUBSTRING(@ValidCharacters, @RandomNumberInt, 1)

        SET @counter = @counter + 1

        SET @RandomID = @RandomID + @CurrentCharacter

END

SELECT @output = @RandomID

And then call it like this:

DECLARE @password VARCHAR(8)
EXEC sp_GeneratePassword @Length = 8, @output = @password OUTPUT

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Mondeo
Constraint Violating Yak Guru

287 Posts

Posted - 2008-12-08 : 17:01:39

Thank you !!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources