newid() error

[mistektips]

Hi,
I am trying to run this stored procedure, but I am getting an error saying:
Server: Msg 170, Level 15, State 1, Line 2
Line 2: Incorrect syntax near ')'.

The stored procedure is as follows:

CREATE PROCEDURE usp_Insert_Met_File_New
@GUID uniqueidentifier,
@Class_Code char(5),
@Sap_Class_Code char(8),
@Effective_Date numeric(8),
@Eff_Amend_No char(4),
@Class_Title char(50),
@Abolish_Date numeric(8),
@Abol_Amend_no char(4),
@Nxt_Low_Class1 char(5),
@Nxt_Low_Class2 char(5),
@Nxt_Low_Class3 char(5),
@Nxt_Low_Class4 char(5),
@Sap_Nxt_Low_Class1 char(8),
@Sap_Nxt_Low_Class2 char(8),
@Sap_Nxt_Low_Class3 char(8),
@Sap_Nxt_Low_Class4 char(8),
@Central_Class char(1),
@Probation_Period numeric(2),
@Coll_Brg_Unit char(1),
@Coll_Brg_Sup char(1),
@Coll_Brg_Code char(2),
@Pay_Range char(2),
@Min_Pay_Step char(2),
@Min_Annual_Sal numeric(6),
@Max_Pay_Step char(2),
@Max_Annual_Sal numeric(6),
@Pay_Sched char(2),
@Leave_Part_Code char(1),
@Age_50_Ret_Code char(1),
@Occupation_Code char(4),
@Trainee_Code char(1),
@Deactivate_Code char(1),
@Pms_Overide_Code char(1),
@Residency_Waived char(1),
@Job_Category_Code char(1),
@Tdd_Unit_Code char(1),
@Position_Coverage_1 char(1),
@Position_Coverage_2 char(1),
@Cs_Coverage_Date numeric(8),
@Class_Creation_Date numeric(8),
@Record_Status char(1),
@Amend_Reason_Code char(2)
AS
INSERT INTO Met_File_New
(
Guid,
Class_Code,
Sap_Class_Code,
Effective_Date,
Eff_Amend_No,
Class_Title,
Abolish_Date,
Abol_Amend_no,
Nxt_Low_Class1,
Nxt_Low_Class2,
Nxt_Low_Class3,
Nxt_Low_Class4,
Sap_Nxt_Low_Class1,
Sap_Nxt_Low_Class2,
Sap_Nxt_Low_Class3,
Sap_Nxt_Low_Class4,
Central_Class,
Probation_Period,
Coll_Brg_Unit,
Coll_Brg_Sup,
Coll_Brg_Code,
Pay_Range,
Min_Pay_Step,
Min_Annual_Sal,
Max_Pay_Step,
Max_Annual_Sal,
Pay_Sched,
Leave_Part_Code,
Age_50_Ret_Code,
Occupation_Code,
Trainee_Code,
Deactivate_Code,
Pms_Overide_Code,
Residency_Waived,
Job_Category_Code,
Tdd_Unit_Code,
Position_Coverage_1,
Position_Coverage_2,
Cs_Coverage_Date,
Class_Creation_Date,
Record_Status,
Amend_Reason_Code
)
VALUES
(
@Guid,
@Class_Code,
@Sap_Class_Code,
@Effective_Date,
@Eff_Amend_No,
@Class_Title,
@Abolish_Date,
@Abol_Amend_no,
@Nxt_Low_Class1,
@Nxt_Low_Class2,
@Nxt_Low_Class3,
@Nxt_Low_Class4,
@Sap_Nxt_Low_Class1,
@Sap_Nxt_Low_Class2,
@Sap_Nxt_Low_Class3,
@Sap_Nxt_Low_Class4,
@Central_Class,
@Probation_Period,
@Coll_Brg_Unit,
@Coll_Brg_Sup,
@Coll_Brg_Code,
@Pay_Range,
@Min_Pay_Step,
@Min_Annual_Sal,
@Max_Pay_Step,
@Max_Annual_Sal,
@Pay_Sched,
@Leave_Part_Code,
@Age_50_Ret_Code,
@Occupation_Code,
@Trainee_Code,
@Deactivate_Code,
@Pms_Overide_Code,
@Residency_Waived,
@Job_Category_Code,
@Tdd_Unit_Code,
@Position_Coverage_1,
@Position_Coverage_2,
@Cs_Coverage_Date,
@Class_Creation_Date,
@Record_Status,
@Amend_Reason_Code
)
GO

I am executing the stored proc as follows:
exec usp_Insert_Met_file_New
newid(),null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null

I am getting an error saying:
Server: Msg 170, Level 15, State 1, Line 2
Line 2: Incorrect syntax near ')'.

When I try to insert into the table directly without using stored proc, it works correctly,
but when i use the stored proc, i get the above mentioned error...
Could you suggest where is the mistake?

 

[vongrunt]

How about assigning NEWID() to variable and then doing:

exec usp_Insert_Met_file_New
@variable, <buncha null values>

?

------
[small]select stuff(stuff(replicate('<P> <B> ', 14), 109, 0, '<.'), 112, 0, '/')[/small]

 

[SQLDenis]

same as with getdate() assign to variable or do this

VALUES
(
NEWID(),
@Class_Code,
@Sap_Class_Code,


Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[mistektips]

I tried this...
But I get the same error...this time when saving the stored procedure

 

[bborissov]

Isn't it easier to put default values for parameters, instead every time you want to pass only first 2,3 or more parameter to add (like vongrunt said) "buncha null values"
Something like:

CREATE PROCEDURE usp_Insert_Met_File_New
    @GUID uniqueidentifier = NewId(),
    @Class_Code char(5) = NULL,
    @Sap_Class_Code char(8) = NULL,
etc.

then just

exec usp_Insert_Met_file_New

Borislav Borissov

 

[mistektips]

Denis,
What should be the format which I run the stored proc if i do NEWID() in the stored proc itself...
i tried the following:
exec usp_Insert_Met_file_New
@guid,null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null

exec usp_Insert_Met_file_New
newid(),null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null

but i still keep getting errors

 

[SQLDenis]

this should work

exec usp_Insert_Met_file_New
null,null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null


But I suggest you implement bborissov's suggestion so that you don't have to pass in a bunch of unknowns

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[mistektips]

Denis,
Could you plz tell me why are we passing a null value for guid in the above way of executing the stored proc?

 

[SQLDenis]

you have to pass something since you don't have a default
and even if you had a default you are calling the proc with positional parameters not named parameters

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[mistektips]

In this case, why am i getting an error if i pass newid() to guid while executing the stored proc?

this stored proc is going to be used by a front end application and they are going to pass newid() to guid in the application...

 

[SQLDenis]

you can't pass newid()
you have to do this

declare @GUID uniqueidentifier
select @GUID =newid()

exec usp_Insert_Met_file_New
@GUID ,null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null

or remove it from the proc and change this
VALUES
(
@Guid,
@Class_Code,


to

VALUES
(
NEWID(),
@Class_Code,



Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[mistektips]

Denis,

I tried the first way that you mentioned above:
declare @GUID uniqueidentifier
select @GUID =newid()
exec usp_Insert_Met_File_New @Guid

I get this error:
Server: Msg 8114, Level 16, State 4, Procedure usp_Insert_Met_File_New, Line 0
Error converting data type uniqueidentifier to char.

 

[SQLDenis]

If you do that then your proc has to start like this

CREATE PROCEDURE usp_Insert_Met_File_New
@GUID uniqueidentifier,


Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[SQLDenis]

if this is your proc

CREATE PROCEDURE usp_Insert_Met_File_New 
    @GUID uniqueidentifier, 
    @Class_Code char(5),
    @Sap_Class_Code char(8),
    @Effective_Date numeric(8),
    @Eff_Amend_No char(4),
    @Class_Title char(50),
    @Abolish_Date numeric(8),
    @Abol_Amend_no char(4),
    @Nxt_Low_Class1 char(5),
    @Nxt_Low_Class2 char(5),
    @Nxt_Low_Class3 char(5),
    @Nxt_Low_Class4 char(5),
    @Sap_Nxt_Low_Class1 char(8),
    @Sap_Nxt_Low_Class2 char(8),
    @Sap_Nxt_Low_Class3 char(8),
    @Sap_Nxt_Low_Class4 char(8),
    @Central_Class char(1),
    @Probation_Period numeric(2),
    @Coll_Brg_Unit char(1),
    @Coll_Brg_Sup char(1),
    @Coll_Brg_Code char(2),
    @Pay_Range char(2),
    @Min_Pay_Step char(2),
    @Min_Annual_Sal numeric(6),
    @Max_Pay_Step char(2),
    @Max_Annual_Sal numeric(6),
    @Pay_Sched char(2),
    @Leave_Part_Code char(1),
    @Age_50_Ret_Code char(1),
    @Occupation_Code char(4),
    @Trainee_Code char(1),
    @Deactivate_Code char(1),
    @Pms_Overide_Code char(1),
    @Residency_Waived char(1),
    @Job_Category_Code char(1),
    @Tdd_Unit_Code char(1),
    @Position_Coverage_1 char(1),
    @Position_Coverage_2 char(1),
    @Cs_Coverage_Date numeric(8),
    @Class_Creation_Date numeric(8),
    @Record_Status char(1),
    @Amend_Reason_Code char(2)
AS
    INSERT INTO Met_File_New
    (
    Guid, 
    Class_Code,
    Sap_Class_Code,
    Effective_Date,
    Eff_Amend_No,
    Class_Title,
    Abolish_Date,
    Abol_Amend_no,
    Nxt_Low_Class1,
    Nxt_Low_Class2,
    Nxt_Low_Class3,
    Nxt_Low_Class4,
    Sap_Nxt_Low_Class1,
    Sap_Nxt_Low_Class2,
    Sap_Nxt_Low_Class3,
    Sap_Nxt_Low_Class4,
    Central_Class,
    Probation_Period,
    Coll_Brg_Unit,
    Coll_Brg_Sup,
    Coll_Brg_Code,
    Pay_Range,
    Min_Pay_Step,
    Min_Annual_Sal,
    Max_Pay_Step,
    Max_Annual_Sal,
    Pay_Sched,
    Leave_Part_Code,
    Age_50_Ret_Code,
    Occupation_Code,
    Trainee_Code,
    Deactivate_Code,
    Pms_Overide_Code,
    Residency_Waived,
    Job_Category_Code,
    Tdd_Unit_Code,
    Position_Coverage_1,
    Position_Coverage_2,
    Cs_Coverage_Date,
    Class_Creation_Date,
    Record_Status,
    Amend_Reason_Code
    )
    VALUES
     (
    @Guid,
    @Class_Code,
    @Sap_Class_Code,
    @Effective_Date,
    @Eff_Amend_No,
    @Class_Title,
    @Abolish_Date,
    @Abol_Amend_no,
    @Nxt_Low_Class1,
    @Nxt_Low_Class2,
    @Nxt_Low_Class3,
    @Nxt_Low_Class4,
    @Sap_Nxt_Low_Class1,
    @Sap_Nxt_Low_Class2,
    @Sap_Nxt_Low_Class3,
    @Sap_Nxt_Low_Class4,
    @Central_Class,
    @Probation_Period,
    @Coll_Brg_Unit,
    @Coll_Brg_Sup,
    @Coll_Brg_Code,
    @Pay_Range,
    @Min_Pay_Step,
    @Min_Annual_Sal,
    @Max_Pay_Step,
    @Max_Annual_Sal,
    @Pay_Sched,
    @Leave_Part_Code,
    @Age_50_Ret_Code,
    @Occupation_Code,
    @Trainee_Code,
    @Deactivate_Code,
    @Pms_Overide_Code,
    @Residency_Waived,
    @Job_Category_Code,
    @Tdd_Unit_Code,
    @Position_Coverage_1,
    @Position_Coverage_2,
    @Cs_Coverage_Date,
    @Class_Creation_Date,
    @Record_Status,
    @Amend_Reason_Code
    )
GO

you call it like this

declare @GUID uniqueidentifier
select @GUID =newid()

exec usp_Insert_Met_file_New 
@GUID ,null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null

or you can do this

CREATE PROCEDURE usp_Insert_Met_File_New 
    @Class_Code char(5),
    @Sap_Class_Code char(8),
    @Effective_Date numeric(8),
    @Eff_Amend_No char(4),
    @Class_Title char(50),
    @Abolish_Date numeric(8),
    @Abol_Amend_no char(4),
    @Nxt_Low_Class1 char(5),
    @Nxt_Low_Class2 char(5),
    @Nxt_Low_Class3 char(5),
    @Nxt_Low_Class4 char(5),
    @Sap_Nxt_Low_Class1 char(8),
    @Sap_Nxt_Low_Class2 char(8),
    @Sap_Nxt_Low_Class3 char(8),
    @Sap_Nxt_Low_Class4 char(8),
    @Central_Class char(1),
    @Probation_Period numeric(2),
    @Coll_Brg_Unit char(1),
    @Coll_Brg_Sup char(1),
    @Coll_Brg_Code char(2),
    @Pay_Range char(2),
    @Min_Pay_Step char(2),
    @Min_Annual_Sal numeric(6),
    @Max_Pay_Step char(2),
    @Max_Annual_Sal numeric(6),
    @Pay_Sched char(2),
    @Leave_Part_Code char(1),
    @Age_50_Ret_Code char(1),
    @Occupation_Code char(4),
    @Trainee_Code char(1),
    @Deactivate_Code char(1),
    @Pms_Overide_Code char(1),
    @Residency_Waived char(1),
    @Job_Category_Code char(1),
    @Tdd_Unit_Code char(1),
    @Position_Coverage_1 char(1),
    @Position_Coverage_2 char(1),
    @Cs_Coverage_Date numeric(8),
    @Class_Creation_Date numeric(8),
    @Record_Status char(1),
    @Amend_Reason_Code char(2)
AS
    INSERT INTO Met_File_New
    (
    Guid, 
    Class_Code,
    Sap_Class_Code,
    Effective_Date,
    Eff_Amend_No,
    Class_Title,
    Abolish_Date,
    Abol_Amend_no,
    Nxt_Low_Class1,
    Nxt_Low_Class2,
    Nxt_Low_Class3,
    Nxt_Low_Class4,
    Sap_Nxt_Low_Class1,
    Sap_Nxt_Low_Class2,
    Sap_Nxt_Low_Class3,
    Sap_Nxt_Low_Class4,
    Central_Class,
    Probation_Period,
    Coll_Brg_Unit,
    Coll_Brg_Sup,
    Coll_Brg_Code,
    Pay_Range,
    Min_Pay_Step,
    Min_Annual_Sal,
    Max_Pay_Step,
    Max_Annual_Sal,
    Pay_Sched,
    Leave_Part_Code,
    Age_50_Ret_Code,
    Occupation_Code,
    Trainee_Code,
    Deactivate_Code,
    Pms_Overide_Code,
    Residency_Waived,
    Job_Category_Code,
    Tdd_Unit_Code,
    Position_Coverage_1,
    Position_Coverage_2,
    Cs_Coverage_Date,
    Class_Creation_Date,
    Record_Status,
    Amend_Reason_Code
    )
    VALUES
     (
    NEWID(),
    @Class_Code,
    @Sap_Class_Code,
    @Effective_Date,
    @Eff_Amend_No,
    @Class_Title,
    @Abolish_Date,
    @Abol_Amend_no,
    @Nxt_Low_Class1,
    @Nxt_Low_Class2,
    @Nxt_Low_Class3,
    @Nxt_Low_Class4,
    @Sap_Nxt_Low_Class1,
    @Sap_Nxt_Low_Class2,
    @Sap_Nxt_Low_Class3,
    @Sap_Nxt_Low_Class4,
    @Central_Class,
    @Probation_Period,
    @Coll_Brg_Unit,
    @Coll_Brg_Sup,
    @Coll_Brg_Code,
    @Pay_Range,
    @Min_Pay_Step,
    @Min_Annual_Sal,
    @Max_Pay_Step,
    @Max_Annual_Sal,
    @Pay_Sched,
    @Leave_Part_Code,
    @Age_50_Ret_Code,
    @Occupation_Code,
    @Trainee_Code,
    @Deactivate_Code,
    @Pms_Overide_Code,
    @Residency_Waived,
    @Job_Category_Code,
    @Tdd_Unit_Code,
    @Position_Coverage_1,
    @Position_Coverage_2,
    @Cs_Coverage_Date,
    @Class_Creation_Date,
    @Record_Status,
    @Amend_Reason_Code
    )
GO

and call it like this

exec usp_Insert_Met_file_New 
null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[mistektips]

Thanks Denis!