create procedure gets stuck on select

Question

create procedure gets stuck on select

Senchi

Ten Centuries

Points: 1184
More actions
January 2, 2018 at 7:19 pm

#325282

CREATE PROCEDURE sp_cenik as begin
DECLARE @OD DATETIME, @DO DATETIME, @TipSobe VARCHAR(6), @KodaCenika INT, @Sezona INT
SELECT @Od = :a2, @Do = :a3, @TipSobe = :a1, @KodaCenika = :a4;
I get:
"Msg 102, Level 15, State 1, Procedure sp_cenik, Line 18
Incorrect syntax near ':'.
What am I doing wrong ? These are parameters.

Viewing 7 posts - 1 through 6 (of 6 total)

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

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 1

Senchi - Tuesday, January 2, 2018 7:19 PM
CREATE PROCEDURE sp_cenik as begin DECLARE @OD DATETIME, @DO DATETIME, @TipSobe VARCHAR(6), @KodaCenika INT, @Sezona INT
SELECT @Od = :a2, @Do = :a3, @TipSobe = :a1, @KodaCenika = :a4;
I get:
"Msg 102, Level 15, State 1, Procedure sp_cenik, Line 18
Incorrect syntax near ':'.
What am I doing wrong ? These are parameters.

If they are, in fact, parameters as you say, then that's the wrong way to pass parameters in a stored procedure. Please see the following...
https://docs.microsoft.com/en-us/sql/t-sql/statements/create-procedure-transact-sql

Parameters look like variables that begin with @.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Senchi Ten Centuries Points: 1184 More actions · Answer 2

DECLARE @OD DATETIME, @DO DATETIME, @TipSobe VARCHAR(6), @KodaCenika INT, @Sezona INT,

@p1 DATETIME, @p2 DATETIME, @p3 VARCHAR(6), @p4 INT

SELECT @Od = @p1, @Do = @p2, @TipSobe =@p3, @KodaCenika = @p4

so this would be the way to go ...?

pietlinden SSC Guru Points: 63428 More actions · Answer 3

Could you explain what this does? (Is this Oracle?)
SELECT @Od = :a2, @Do = :a3, @TipSobe = :a1, @KodaCenika = :a4;

Senchi Ten Centuries Points: 1184 More actions · Answer 4

this is sql server 2014 and those are parameters I supply within my application.

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

No, that's declaring variables. (and no idea what the unknown language is doing)

Please have a look at the T-SQL documentation for creating procedures: https://docs.microsoft.com/en-us/sql/t-sql/statements/create-procedure-transact-sql

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

Grant Fritchey SSC Guru Points: 398690 More actions · Answer 6

Senchi - Tuesday, January 2, 2018 7:19 PM
CREATE PROCEDURE sp_cenik as begin DECLARE @OD DATETIME, @DO DATETIME, @TipSobe VARCHAR(6), @KodaCenika INT, @Sezona INT
SELECT @Od = :a2, @Do = :a3, @TipSobe = :a1, @KodaCenika = :a4;
I get:
"Msg 102, Level 15, State 1, Procedure sp_cenik, Line 18
Incorrect syntax near ':'.
What am I doing wrong ? These are parameters.

So, the error message says that your syntax is messed up at the colon, this thing, : . You have colons on your code. T-SQL doesn't use colons in syntax like that. This is why everyone is having a hard time helping.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning