Stored Procedure Errors - SQL Server Forums

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

SQL Server 2008 Forums

Other SQL Server 2008 Topics

Stored Procedure Errors

Author

Topic

boywonder115
Starting Member

5 Posts

Posted - 2014-12-30 : 16:34:21

I'm creating a new stored procedure:

Use CoopUSDev
go

CREATE PROCEDURE sp_Resources12
(
@msg varchar
)

AS
BEGIN

@msg = "Select * from tblresources where active = 1 and page = 'Resources'"
/* msg = msg and usertype like ('%" & session("txtusertype") & "%')" */

BEGIN
if isPaeDealer = true
@msg = @msg & " and (usertype like ('%" & ("txtusertype") & "%') or usertype like '%pae%') "
else
@msg = @msg & " and usertype like ('%" & ("txtusertype") & "%')"
end if
END

BEGIN
@msg = @msg & " and columnNumber = 1"
@msg = @msg & " and dtmExpire > getdate()"
END

BEGIN
if ("ysnOA")="0" /* user does not have OA funds so do not show OA options */
@msg = @msg & " and (ysnOA <> 1 or ysnOA is null)"
end if
END

BEGIN
if ("ysnPae")="1" /* user does not have OA funds so do not show OA options */
@msg = @msg & " and ((ysnPae =1) and ysnPae is not null) "
end if
END

/*This section was added to give the ability to have documents only shown to Pae and no one else. */
BEGIN
if ("ysnPae")="1"
@msg = @msg & " and (PAEonly =0 or PAEonly is null) "
end if
END

BEGIN
/* ENd This section was added to give the ability to have documents only shown to Pae and no one else.
if lcase(session("txtusertype"))="dealer" and session("ysnJa")<>1 then 'user is not a JA dealer so hide
JA-exclusive links (ysnJaOnly in tblResources)*/
@msg = @msg & " and (ysnJaOnly <> 1 or ysnJaOnly is null)"

@msg = @msg & " order by txtGroupLabel,sortorder"
END

END
GO

- - - - - - - - - - - -

And I'm receiving the following issues:

Msg 102, Level 15, State 1, Procedure sp_Resources12, Line 10
Incorrect syntax near '@msg'.
Msg 102, Level 15, State 1, Procedure sp_Resources12, Line 15
Incorrect syntax near '@msg'.
Msg 102, Level 15, State 1, Procedure sp_Resources12, Line 28
Incorrect syntax near '@msg'.
Msg 102, Level 15, State 1, Procedure sp_Resources12, Line 34
Incorrect syntax near '@msg'.
Msg 102, Level 15, State 1, Procedure sp_Resources12, Line 41
Incorrect syntax near '@msg'.

Don't know what I'm doing wrong. Please help...

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-12-30 : 16:37:21

You need to use SET or SELECT with your variables, like this:

SET @msg = 'some message'

Also, the concatenation character is +, not &.

I don't understand why you are building a dynamic query for something that doesn't appear to need to be dynamic.

Seems like you'd need to make @msg an OUTPUT parameter, right now it's only INPUT.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

boywonder115
Starting Member

5 Posts

Posted - 2014-12-31 : 11:18:07

OK, I have shorten the SP to:

IF isPaeDealer = FALSE THEN
SET @msg = @msg + usertype like '%' + session("txtusertype") + '%'

SET @msg = @msg + [columnNumber] = 1
SET @msg = @msg + [dtmExpire] > getdate()

SELECT @msg = @msg + ORDER BY txtGroupLabel, sortorder

- - - - -
But now I'm getting the following errors:

Msg 156, Level 15, State 1, Procedure sp_Resources12a, Line 20
Incorrect syntax near the keyword 'THEN'.
Msg 156, Level 15, State 1, Procedure sp_Resources12a, Line 21
Incorrect syntax near the keyword 'like'.
Msg 102, Level 15, State 1, Procedure sp_Resources12a, Line 23
Incorrect syntax near '='.
Msg 102, Level 15, State 1, Procedure sp_Resources12a, Line 24
Incorrect syntax near '>'.
Msg 156, Level 15, State 1, Procedure sp_Resources12a, Line 26
Incorrect syntax near the keyword 'ORDER'.

boywonder115
Starting Member

5 Posts

Posted - 2014-12-31 : 11:19:57

I apologize.. I've been using MS Access and have lost a few years experience in SQL Server.

boywonder115
Starting Member

5 Posts

Posted - 2014-12-31 : 12:24:22

Just changed it again to:

IF isPaeDealer = TRUE THEN

SELECT
[sortorder],
[page],
[txtgroup],
[txtGroupLabel],
[usertype],
[active],
[dtmExpire],
[columnNumber]

FROM dbo.tblResources

WHERE

[columnNumber] = 1 + [dtmExpire] > getdate()

ORDER
txtGroupLabel, sortorder

- - - - - - -

And now getting the following error:
Msg 156, Level 15, State 1, Procedure sp_Resources12a, Line 16
Incorrect syntax near the keyword 'THEN'.
Msg 102, Level 15, State 1, Procedure sp_Resources12a, Line 32
Incorrect syntax near '>'.
Msg 156, Level 15, State 1, Procedure sp_Resources12a, Line 40
Incorrect syntax near the keyword 'THEN'.
Msg 195, Level 15, State 10, Procedure sp_Resources12a, Line 56
'session' is not a recognized built-in function name.

- - - - -
Look like I'm getting closer, but still getting errors..

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-12-31 : 14:17:20

I'm going to have to refer you to Books Online as your syntax is just not correct. What is isPaeDealer? Is it a variable or a column?

Here's an example IF:

IF @var1 = 0
BEGIN
SELECT ......
END
ELSE
BEGIN
SELECT .....
END

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

boywonder115
Starting Member

5 Posts

Posted - 2014-12-31 : 15:47:02

I figured it out:

ALTER PROCEDURE [dbo].[procGetResourcesNew]
-- Add the parameters for the stored procedure here
(
@UserType VARCHAR(20),
@Page varchar(20),
@ColumnNumber int
)

AS
BEGIN

SELECT
[sortorder],
[page],
[txtgroup],
[txtGroupLabel],
[usertype],
[active],
[dtmExpire],
[columnNumber]
FROM dbo.tblResources
WHERE
[usertype] like @UserType
and [page] = @Page
and [columnNumber] = @ColumnNumber
and [dtmExpire] > getdate()
and [active] = 1
ORDER BY
txtGroupLabel, sortorder

- - - - - - - - - - - - - -- - -

Thanks tkizer

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-12-31 : 17:11:36

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources