Value from Query
-
I have the monthname as fieldnames in a table. And the datatype is numeric.It will have values 1 or 2.
i need to select the value for the given month in a stored procedure. the monthname will be passes as parameter
Create storedprocedure UspCheck as(@Month varchar(15))
as
declare @test-2 numeric(10,2)
Select @test-2 = @Month from LockDatabase where CompanyID='BM' and Finyear='2006-2007'
if @test-2 =1 then
print 'One'
else
print 'Two'
But the select stmt does not work. It takes the value of the @month field and datatype conversion error occurs.
Whats the work around?
Rohini
-
y r u using the below statement ? what is the result u r expecting from the below query ??
Select @test-2 = @Month from LockDatabase where CompanyID='BM' and Finyear='2006-2007'
Will this work ???
select @test-2 = monthname from LockDatabase where CompanyID='BM' and Finyear='2006-2007' and monthname = @Month
-
What is the Datatype error you are getting?
SELECT CASE MonthName
WHEN
1
THEN
'One'
WHEN
2
THEN
'Two'
END As MonthName
FROM
LockDatabase
WHERE
CompanyID='BM'
AND
Finyear='2006-2007'
AND
MonthName = @Month
Hope this helps...
Prasad Bhogadi
www.inforaise.com -
What do u mean the monthname here ? A fieldname or parameter or command? or what is it?
Eg:
The table structure is
CompanyID varchar(10)
Finyear varchar(10)
April numeric(10,2)
May numeric(10,2)
June numeric(10,2)
July numeric(10,2)
and the data is
BM 2004-2005 1 0 1 1
EM 2005-2006 1 0 1 0
I want to select the value of the fields April or May or june or...... based on the parameter.
So @Month is the parameter
-
First of all why do you Numeric as datatype when you are saving INT value, you can have it a BIT or SMALLINT and from your post I believe you will be passing either 'April','May','June' or 'July' to your stored procedure as month. Dynamic SQL is not advised but it should give you a immediate workaround.
CREATE PROCEDURE MyProc
@Month VARCHAR(12)
AS
DECLARE @Qry VARCHAR(1000)
DECLARE @Month VARCHAR(12)
SET @Month = 'July'
SET @Qry = 'SELECT CASE ' + @Month + ' WHEN 1 THEN ''ONE''
ELSE ''TWO'' END AS Month
FROM
LockDatabase
WHERE
CompanyID=''BM''
AND
Finyear=''2006-2007'''
EXEC (@Qry)
Prasad Bhogadi
www.inforaise.com -
CREATE PROCEDURE MyProc
@Month VARCHAR(12)
AS
DECLARE @Qry VARCHAR(1000)
--SET @Month = 'July'
SET @Qry = 'SELECT CASE ' + @Month + ' WHEN 1 THEN ''ONE''
ELSE ''TWO'' END AS MonthName
FROM
LockDatabase
WHERE
CompanyID=''BM''
AND
Finyear=''2006-2007'''
EXEC (@Qry)
Prasad Bhogadi
www.inforaise.com -
What will be the output of ur proc?
I want the value of the field Apr or May or June whatever it is..
Based on the value,i will perform some action..not just 'one' and 'two'. thats just a sample. And even if it is 'One' and 'Two' where will the ouput be printed in ur procedure ?
-
The output will be returned as MonthName as part of the resultset, do you want it to be printed?
Prasad Bhogadi
www.inforaise.com -
i want the output to a variable . Only in the back end.
-
check whether this works...
CREATE PROCEDURE USPCHECK_TEST (@MONTH VARCHAR(15))
AS
BEGIN
SET NOCOUNT ON
DECLARE @test-2 NUMERIC(10,2)
DECLARE @sql VARCHAR(2000)
IF OBJECT_ID('TEMPDB..##TMP') IS NOT NULL
BEGIN
TRUNCATE TABLE ##TMP
DROP TABLE ##TMP
END
SET @sql = 'SELECT '+ @MONTH +' AS MONTH INTO ##TMP FROM LOCKDATABASE WHERE CID=''BM'' AND FINYEAR=''2006-2007'''
EXEC(@SQL)
SELECT @test-2 = MONTH FROM ##TMP
SET NOCOUNT OFF
PRINT @test-2
END
USPCHECK_TEST 'APRIL'
-
Hey ganesh. Great ! this works fine.
Thanks a lot.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply