SQL Server : Issue with variable and stored procedure

Question

I have the following stored procedure

CREATE PROCEDURE SigleEnseigne
     @Sigle VARCHAR OUTPUT,
     @Enseigne VARCHAR OUTPUT,
     @SigleEnseigne1 VARCHAR(150) OUTPUT
AS
BEGIN
    SELECT ISNULL(John_Jack.Sigle,'') 
    INTO Sigle 
    FROM John_Jack

    SELECT ISNULL(John_Jack.Enseigne,'') 
    INTO Enseigne 
    FROM John_Jack

    SELECT 
        CASE 
           WHEN Code_Juridique LIKE 'M%' THEN '      / ' + @Enseigne INTO @SigleEnseigne1
           WHEN @Sigle = '' AND @Enseigne = '' THEN '' INTO @SigleEnseigne1
           ELSE @Sigle + ' / ' + @Enseigne INTO @SigleEnseigne1
        END
    FROM John_Jack
END
GO

I've got this error code when creating the sp

Msg 156, Niveau 15, État 1, Procédure SigleEnseigne, Ligne 29
Syntaxe incorrecte vers le mot clé 'INTO'.

SQL Server doesn't like this line

WHEN Code_Juridique LIKE 'M%' THEN '      / ' + @Enseigne INTO @SigleEnseigne1

Can anyone give me some insight of what I'm doing wrong, please? I'm fairly new to SQL Server, my script may not respect the T-SQL standard. If so, please enlighten me.

Thanks

@Sigle <> Sigle,also learn to declare the size,VARCHAR is equivalent to VARCAHR(1) — Mihai
– Mihai, Commented Dec 10, 2015 at 10:25
Hi @GiorgiNakeuri, I'm trying to put the relevant column data into the variable Sigle,Enseigne and SigleEnseigne1 , to be able to retrieve them when I'll use the sp in my reports — Andy K
– Andy K, Commented Dec 10, 2015 at 10:29

Giorgi Nakeuri · Accepted Answer · 2015-12-10 10:50:46Z

1

You have several problems here:

1. Give your types a length
2. Incorrect syntax like `INTO @SigleEnseigne1`
3. You can do this in one go

Here is corrected query:

CREATE PROCEDURE SigleEnseigne
    @Sigle VARCHAR(150) OUTPUT ,
    @Enseigne VARCHAR(150) OUTPUT ,
    @SigleEnseigne1 VARCHAR(150) OUTPUT
AS
    BEGIN

        SELECT TOP 1
                @Sigle = ISNULL(Sigle, '') ,
                @Enseigne = ISNULL(Enseigne, '') ,
                @SigleEnseigne1 = CASE WHEN Code_Juridique LIKE 'M%'
                                         THEN '      / ' + ISNULL(Enseigne, '')
                                       WHEN ISNULL(Sigle, '') = ''
                                            AND ISNULL(Enseigne, '') = ''
                                         THEN ''
                                       ELSE Sigle + ' / ' Enseigne
                                  END
        FROM    John_Jack
    END
GO

But this query makes no sense if John_Jack table has more then one row. In such a case you probably need to add some WHERE clause to the query to get the row you need.

edited Dec 10, 2015 at 10:50

answered Dec 10, 2015 at 10:39

Giorgi Nakeuri

35.9k8 gold badges50 silver badges78 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Krishnraj Rana Over a year ago

This could probably the answer of the question. I have not checked the variable length :(

Andy K Over a year ago

@GiorgiNakeuri , I'm creating the sp to use it on a SSRS report. The previous report was built on crystal report and I'm translating it. From what I've read so far, SSRS should be able to put one record at a time. So I'm trying and if it is not doing what I'm lookingfor , I'll find another way

Giorgi Nakeuri Over a year ago

Declare @Sigle VARCHAR(150) , @Enseigne VARCHAR(150) , @SigleEnseigne1 VARCHAR(150) EXEC SigleEnseigne @Sigle OUTPUT, @Enseigne OUTPUT, @SigleEnseigne1 OUTPUT

JonSick · Accepted Answer · 2015-12-10 10:31:12Z

1

It should be more like

WHEN something THEN SET @yourVariableField = whatever

The INTO statement is more if you're inserting data into a table or table variable

answered Dec 10, 2015 at 10:31

JonSick

1389 bronze badges

Comments

Krishnraj Rana · Accepted Answer · 2015-12-10 10:35:00Z

1

It should be like this -

SELECT @Sigle = ISNULL(John_Jack.Sigle,'') 
FROM John_Jack;

SELECT @Enseigne = ISNULL(John_Jack.Enseigne,'') 
FROM John_Jack;

SELECT @SigleEnseigne1 = CASE 
                WHEN Code_Juridique LIKE 'M%' THEN '      / ' + @Enseigne 
                WHEN @Sigle = '' AND @Enseigne = '' THEN '' 
                ELSE @Sigle + ' / ' + @Enseigne
                END
 FROM John_Jack

As you define output variable @SigleEnseigne1 VARCHAR(150) OUTPUT.

edited Dec 10, 2015 at 10:35

answered Dec 10, 2015 at 10:26

Krishnraj Rana

6,6483 gold badges33 silver badges37 bronze badges

3 Comments

Giorgi Nakeuri Over a year ago

You are just dialing with syntax problem. OP has not only the syntax problem.

Krishnraj Rana Over a year ago

@GiorgiNakeuri: Because OP is define output variable as varchar.

Andy K Over a year ago

Hi @KrishnrajRana , First, Thank You. Please see my comments below my question

Collectives™ on Stack Overflow

SQL Server : Issue with variable and stored procedure

3 Answers 3

3 Comments

Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related