Problem with executing procedure

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

General SQL Server Forums

New to SQL Server Programming

Problem with executing procedure

Author

Topic

john20
Starting Member

30 Posts

Posted - 2008-09-17 : 10:33:16

Hi All,

I am trying to execute the procedure in query analyzer but it is giving me error message, can you please tell me where i am doing wrong.

prodcedure is :

CREATE PROC [User].[kuafGroupID]

(

@Root nvarchar(25),@TableName nvarchar(25), @UserID smallint output

)

AS

BEGIN

SET NOCOUNT ON

DECLARE @DataID int, @iDataID int

SET @iDataID = (SELECT distinct childID FROM kuafchildren WHERE Childid = @Root)

SET @DataID = (SELECT MIN(ID) FROM kuafchildren WHERE ChildID = @Root)

Exec('insert into ' + @TableName + '(DataID) values(' + @iDataID + ')')

select @UserID = 1

WHILE @DataID IS NOT NULL

BEGIN

EXEC GetDataID @DataID,@TableName,@UserID

SET @DataID = (SELECT MIN(ID) FROM kuafchildren WHERE ChildID = @Root AND ID > @DataID)

END

END

and i am executing like :

DECLARE @NewId smallint

Exec kuafGroupID 3053,groupid,@NewId output

Error is :

Msg 102, Level 15, State 1, Line 1
Incorrect syntax near ')'.

can you please help.

Thanks in advance

-john

afrika
Master Smack Fu Yak Hacker

2706 Posts

Posted - 2008-09-17 : 10:51:01

change
CREATE PROC [User].[kuafGroupID]

to

CREATE PROC [dbo].[kuafGroupID]

or when you try to execute it use
execute [user].[kuafGroupID]

But use the above example. Much better

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2008-09-17 : 12:12:01

Try changing proc as follows (I've only included the part that I changed)

BEGIN

SET NOCOUNT ON

DECLARE @DataID int, @iDataID int = 0, @sSQL NVARCHAR(1000)

SET @iDataID = (SELECT distinct childID FROM kuafchildren WHERE Childid = @Root)
SET @DataID = (SELECT MIN(ID) FROM kuafchildren WHERE ChildID = @Root)

SET @sSQL = 'insert into ' + @TableName + '(DataID) values(' + CAST(@iDataID AS VARCHAR(10)) + ')'
Exec(@sSQL)

--
Gail Shaw
SQL Server MVP

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources