Procedure parameter error - 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

General SQL Server Forums

New to SQL Server Programming

Procedure parameter error

Author

Topic

mavershang
Posting Yak Master

111 Posts

Posted - 2009-05-19 : 13:17:17

Hi all. Here is my question:

The procedure is like
================
CREATE PROC [dbo].[sp_NBestPlusCPE]
@TargetTable AS nvarchar(255) = NULL,
@CPE_Threshold AS float=0
AS
BEGIN
DECLARE @TnameIn nvarchar(255), @SQL nvarchar(4000)
SET @TnameIn = QUOTENAME(@TargetTable)
SET @SQL = '
SELECT ThermCol1, ThermCol2
FROM '+ @TnameIn+'
WHERE CovaryPctOfEvents>=@CPE_Threshold
'
EXEC (@SQL)
END
============================

then I execute it
=============================
DECLARE @TargetTable nvarchar(255)
DECLARE @CPE_Threshold_test float

SET @TargetTable = 'ECResult'
SET @CPE_Threshold_test = 50

EXECUTE [crwdb].[dbo].[sp_NBestPlusCPE]
@TargetTable,
@CPE_Threshold_test
==============================

It keep saying "Must declare the scalar variable "@CPE_Threshold""
But I think I have declared it.

What is the problem with it?

Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-05-19 : 13:22:10

did you mean this?

CREATE PROC [dbo].[sp_NBestPlusCPE]
@TargetTable AS nvarchar(255) = NULL,
@CPE_Threshold AS float=0
AS
BEGIN
DECLARE @TnameIn nvarchar(255), @SQL nvarchar(4000)
SET @TnameIn = QUOTENAME(@TargetTable)
SET @SQL = '
SELECT ThermCol1, ThermCol2 
FROM '+ @TnameIn+'
WHERE CovaryPctOfEvents>= ' + @CPE_Threshold

EXEC (@SQL)
END

mavershang
Posting Yak Master

111 Posts

Posted - 2009-05-19 : 13:26:23

Yes. Though the code you modified will say "cannot convert float to nvarchar". But it works if declare the @CPE_Threshold as nvarchar.

I am just wandering why the error "@CPE_Threshold need to be declared " pump out if I declare it as float

Thanks for reply

quote:

Originally posted by visakh16

did you mean this?

CREATE PROC [dbo].[sp_NBestPlusCPE]
@TargetTable AS nvarchar(255) = NULL,
@CPE_Threshold AS float=0
AS
BEGIN
DECLARE @TnameIn nvarchar(255), @SQL nvarchar(4000)
SET @TnameIn = QUOTENAME(@TargetTable)
SET @SQL = '
SELECT ThermCol1, ThermCol2 
FROM '+ @TnameIn+'
WHERE CovaryPctOfEvents>= ' + @CPE_Threshold

EXEC (@SQL)
END

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-05-19 : 13:29:48

[code]CREATE PROC [dbo].[sp_NBestPlusCPE]
@TargetTable AS nvarchar(255) = NULL,
@CPE_Threshold AS float=0
AS
BEGIN
DECLARE @TnameIn nvarchar(255), @SQL nvarchar(4000)
SET @TnameIn = QUOTENAME(@TargetTable)
SET @SQL = '
SELECT ThermCol1, ThermCol2
FROM '+ @TnameIn+'
WHERE CovaryPctOfEvents>= ' + CAST(@CPE_Threshold AS nvarchar(10))

EXEC (@SQL)
END
[/code]

mavershang
Posting Yak Master

111 Posts

Posted - 2009-05-19 : 13:31:24

just wandering why it does not work if I put float type @CPE_Threshold in the quote marker?

Thanks.

quote:

Originally posted by visakh16

CREATE PROC [dbo].[sp_NBestPlusCPE]
@TargetTable AS nvarchar(255) = NULL,
@CPE_Threshold AS float=0
AS
BEGIN
DECLARE @TnameIn nvarchar(255), @SQL nvarchar(4000)
SET @TnameIn = QUOTENAME(@TargetTable)
SET @SQL = '
SELECT ThermCol1, ThermCol2 
FROM '+ @TnameIn+'
WHERE CovaryPctOfEvents>= ' + CAST(@CPE_Threshold AS nvarchar(10))

EXEC (@SQL)
END

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-05-19 : 13:41:03

you cant concatenate a float value to a nvarchar value. @SQL is nvarchar but @CPE_Threshold is float. hence the error

mavershang
Posting Yak Master

111 Posts

Posted - 2009-05-19 : 13:52:34

But how about this statement: (the $AlnID is int,@covary3Threshold and @showcounts are float
set @SQL = 'insert into ' + @TnameOut + ' exec sp_paircounts @AlnID, @covary3Threshold, @showcounts;'

this one works.

quote:
Originally posted by visakh16

you cant concatenate a float value to a nvarchar value. @SQL is nvarchar but @CPE_Threshold is float. hence the error

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-05-19 : 13:55:24

but you're not using the variables directly here. you're wrapping it inside '' so it all becomes a string value which is ' exec sp_paircounts @AlnID, @covary3Threshold, @showcounts;'

mavershang
Posting Yak Master

111 Posts

Posted - 2009-05-19 : 14:00:48

All right. I got it.
Thanks a lot for your help.

quote:
Originally posted by visakh16

but you're not using the variables directly here. you're wrapping it inside '' so it all becomes a string value which is ' exec sp_paircounts @AlnID, @covary3Threshold, @showcounts;'

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-05-19 : 14:04:31

welcome

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources