Using Parameters in Stored Procedures

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 2000 Forums

SQL Server Development (2000)

Using Parameters in Stored Procedures

Author

Topic

robbath
Starting Member

4 Posts

Posted - 2004-05-26 : 07:22:21

Hi, I don't know if I have posted in the correct place, and I don't know if what I am going to ask is possible. I have a stored procedure which runs a series of calculations upon a column (Q1) and what to run it on a whole series of columns (Q1-Q67). Below you will find my attempt at making this stored procedure step through each column. (I plan to call the stored procedure from within a loop, so don't worry about moving from column to column).

My problem is making use of a parameter within the SELECT clause, as I am getting the following error when I try to check the Syntax:

"Invalid Column Name 'Q'
The Average Aggregate Operation cannot take a varchar as an argument
The Varience Aggregate Operation cannot take a varchar as an argument"

My stored procedure is as follows:

dbo.[360_AllQuestions]
(
@ParticipantId VarChar(31)
)
AS
DECLARE @Question int
DECLARE @Que varchar(2)
SET @Question = 1
SET @Que = "Q" + Cast(@Question As VarChar)
INSERT INTO [360_QuestionResults](Participant, Question, Role, CountOfRole, AverageScore, MinValue, MaxValue, Variance)
SELECT ParticipantId, @Que AS Question, RelationshipToParticipant, COUNT(RelationshipToParticipant) AS ACount, AVG(@Que) AS AAvg, Min(@Que) AS AMin, Max(@Que) AS AMax, VAR(@Que) AS Variance
FROM HLC360Assessment
WHERE ParticipantId = @ParticipantId AND (@Que <> 0)
GROUP BY ParticipantId, RelationshipToParticipant

I hope that somebody may be able to offer me some advice, especially about whether a variable can be used in the SELECT clause (as shown in bold above)....

regards,

Rob Bath

mohdowais
Sheikh of Yak Knowledge

1456 Posts

Posted - 2004-05-26 : 07:53:32

No rob, what you are trying to do wont work, you seem to have confused yourself between data and schema. You can do this using dynamic SQL, but that is not recommended for many reasons, as you will find if you search through these forums for the term "dynamic sql". But this is how you would do it using dynamic sql:


DECLARE @Sqlstr varchar(1000), @sqlstr1 varchar(1000)

SET @SqlStr = 'INSERT INTO [360_QuestionResults](Participant, Question, Role, CountOfRole, AverageScore, MinValue, MaxValue, Variance)
SELECT ParticipantId, @Que AS Question, RelationshipToParticipant, COUNT(RelationshipToParticipant) AS ACount, AVG(@Que) AS AAvg,
Min(@Que) AS AMin, Max(@Que) AS AMax, VAR(@Que) AS Variance
FROM HLC360Assessment
WHERE ParticipantId = @ParticipantId AND (@Que <> 0)
GROUP BY ParticipantId, RelationshipToParticipant'

SET @SqlStr1 = REPLACE(@SqlStr, '@Que', @Que)

EXEC (@SqlStr1)

The keyword here is EXEC, that lets you run a dynamically built sql statement, but it is prone to performance and security issues.

Looks like you could do with a lot more normalization in your database, it appears that you are storing data in columns which should be stored in rows. Let us know what your database looks like and we should be able to suggest something.

EDIT: Fixed some crazy scrolling

OS

robbath
Starting Member

4 Posts

Posted - 2004-05-26 : 08:05:09

you are absolutely right that I needed more normalisation. Given the change I would certainly have split my tables further. Unfortunately much of the problems stemmed from a change of user requirements after the development was well underway...

Anyhow the columns in the HLC360Assessment table are:

RepondentId (Key)
ParticipantId VarChar(31)
FirstName
LastName
RelationshipToParticipant Char(1)
Q1 (stores a real value between 0 and 6)
Q2
Q3
...
Q67

There are several other columns too, but these arn't relevent to this question

Being new to all of this, I don't know whether I need anything special (ie different software etc) to run Dynamic SQL.

(If all else fails I could of course write all 67 stored procedures...)

thanks for you speedie response,

Rob

robbath
Starting Member

4 Posts

Posted - 2004-05-26 : 08:43:05

Sorry to keep bugging people, but I have tried the TransSQL approach suggested by mohdowais, and have adapted it to pass in the @ParticipantId Variable too. However when I am executing it I am getting an error: Invalid Column Name '[value passed into @ParticipantId]' I was also previously getting a maximum length = 128 error. Could anybody cast any light on what I am doing wrong.

Thank you for your help,

Rob

the latest version of the SP:

CREATE PROCEDURE dbo.[360_AllQuestions]
(
@ParticipantId VarChar(31)
)
AS
DECLARE @Sqlstr varchar(1000), @sqlstr1 varchar(1000), @Question int, @Que varchar(2)
SET @Question = 1
SET @Que = "Q" + Cast(@Question As VarChar)
SET @SqlStr = "INSERT INTO [360_QuestionResults](Participant, Question, Role, CountOfRole, AverageScore, MinValue, MaxValue, Variance)
SELECT ParticipantId, @Que AS Question, RelationshipToParticipant, COUNT(RelationshipToParticipant) AS ACount, AVG(@Que) AS AAvg,
Min(@Que) AS AMin, Max(@Que) AS AMax, VAR(@Que) AS Variance
FROM HLC360Assessment
WHERE ParticipantId = @ParticipantId AND (@Que <> 0)
GROUP BY ParticipantId, RelationshipToParticipant"
SET @SqlStr1 = REPLACE(@SqlStr, "@Que", @Que)
SET @SqlStr = REPLACE(@SqlStr1, "@ParticipantId", @ParticipantId)
EXEC (@SqlStr)

Kristen
Test

22859 Posts

Posted - 2004-05-26 : 08:53:22

Won't @ParticipantId have to be quoted in @SqlStr?

Perhaps something like


FROM HLC360Assessment
WHERE ParticipantId = '@ParticipantId' AND (@Que <> 0)
GROUP BY ParticipantId, RelationshipToParticipant"

Are you OK using double quotes for string handling? (Needs SET QUOTED_IDENTIFIER OFF I think)

Kristen

robbath
Starting Member

4 Posts

Posted - 2004-05-26 : 10:24:47

Thanks to both of you, with a bit of fumbling about I have found something that seems to work. Thanks again, Rob

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources