November 18, 2014 at 1:07 pm
I generate scripts of all my stored procedures and when I run on 2008 I get a problem on this one saying something like scalar variable needs to be declared
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Users_New]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[Users_New]
(
@AccountID int
,@PanelCode nvarchar(10) = ''
,@Name nvarchar(30) = ''
,@UserID nvarchar(30) = ''
,@Password nvarchar(30) = ''
,@UserType smallint
,@Picture ntext = ''
,@Note ntext = ''
,@ExpirationDate datetime = null
)
AS
INSERT INTO [User Codes]
(
[AccountID]
,[PanelCode]
,[Name]
,[UserID]
,[Password]
,[UserType]
,[Picture]
,[Note]
,[ExpirationDate]
)
VALUES
(
@AccountID
,@PanelCode
,@Name
,@UserID
,@Password
,@UserType
,@Picture
,@Note
,@ExpirationDate
)
SELECT SCOPE_IDENTITY(),
@@IDENTITY;
'
END
If I take everything out except this it runs fine:
CREATE PROCEDURE [dbo].[Users_New]
(
@AccountID int
,@PanelCode nvarchar(10) = ''
,@Name nvarchar(30) = ''
,@UserID nvarchar(30) = ''
,@Password nvarchar(30) = ''
,@UserType smallint
,@Picture ntext = ''
,@Note ntext = ''
,@ExpirationDate datetime = null
)
AS
INSERT INTO [User Codes]
(
[AccountID]
,[PanelCode]
,[Name]
,[UserID]
,[Password]
,[UserType]
,[Picture]
,[Note]
,[ExpirationDate]
)
VALUES
(
@AccountID
,@PanelCode
,@Name
,@UserID
,@Password
,@UserType
,@Picture
,@Note
,@ExpirationDate
)
SELECT SCOPE_IDENTITY(),
@@IDENTITY;
This is just creating a stored procedure that expects specific parameters. Seems pretty elementary to me.
Thanks in advance,
Mike
November 18, 2014 at 1:40 pm
The first one is dynamic sql and where the parameters have a default value of empty string ('') you need to change it to '''' (four single quotes) because the way SQL Server is currently interpreting the SQL string is as:
CREATE PROCEDURE [dbo].[Users_New]
(
@AccountID int
,@PanelCode nvarchar(10) = '
Then
'
,@Name nvarchar(30) = '
And so on.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
November 18, 2014 at 2:15 pm
I don't know how I missed that. Thanks for the help.
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply