August 10, 2010 at 4:04 am
Try this:
-- Conditionally drop if the procedure already exists
IF OBJECT_ID('dbo.Testing') IS NOT NULL
AND OBJECTPROPERTY(OBJECT_ID('dbo.Testing'), 'IsProcedure') = 1
BEGIN
PRINT 'Dropping Stored procedure Testing...'
DROP PROCEDURE dbo.Testing
END ;
GO
-- Procedure Definition
CREATE PROCEDURE dbo.Testing
@Date DATETIME ,
@Col INT
AS
BEGIN
-- Declaration of Local variables
DECLARE @SQL VARCHAR(MAX)
DECLARE @grp1 VARCHAR(1000)
DECLARE @grp2 VARCHAR(1000)
--DECLARE @Col VARCHAR(10)
DECLARE @SELECTLIST VARCHAR(MAX)
-- Initiation of Vairables
-- SET @Col= 2
SET @grp1= '[Tab1SalesHeader].No, [Tab1SalesLine].Description, [Tab1SalesLine].[Variant Code]'
SET @grp2= '[Tab1SalesHeader].No, [Tab1Sales Line].Description'
-- conditionally check trhe input
IF (@Col=1)
SET @SELECTLIST = @grp1
ELSE
SET @SELECTLIST = @grp2
SET @SQL = 'Select ' + @SELECTLIST + '
, Convert(numeric(10,2)
, SUM([Tab1SalesLine].Quantity)) AS EXPR1
, Count(*) Row_Count
FROM [Tab1SalesHeader]
INNER JOIN [Tab1SalesLine]
ON [Tab1SalesHeader].No = [Tab1SalesLine].[No]
WHERE [Tab1SalesHeader].OrderDate < '+ CAST( @Date AS VARCHAR ) +
' GROUP BY ' + @SELECTLIST
EXEC(@SQL)
END ;
GO
-- Check if the procedure is created sucessfully
IF OBJECT_ID('dbo.Testing') IS NOT NULL
AND OBJECTPROPERTY(OBJECT_ID('dbo.Testing'), 'IsProcedure') = 1
BEGIN
PRINT 'Create Stored procedure Testing Successfully'
END ;
GO
As a side note, please create / author any objects with as much comments as possible.. not only it will help you in debugging it, it will help the future-onlookers of the SP to atleast understand what it does...
Just peep into how i used the conditional checking of drop statements; i guess u just wanted a quick sample and i am sure you will surely have some standard for coding in place in your project..
Hope that helps!
August 10, 2010 at 5:12 am
Dear ColdCoffee, Thanks for your guidance. I wish to say all, tht i am not a proffesional procedure creator. this is my first task given. ll learn soon all things with procedures.
Now after modifying as per your notes, i am still getting the conversion error, so let me tell the nature of that date field as i know.
When its extracted from DB it will like the below,
2010-01-16 00:00:00.000
2010-01-20 00:00:00.000
2010-01-24 00:00:00.000
2010-01-26 00:00:00.000
2010-01-28 00:00:00.000
2010-01-29 00:00:00.000
2010-01-31 00:00:00.000
...............
So i thought to change both the nature of field in the Where condition like th below,
Convert(varchar(20),[Tab1SalesHeader].[Posting Date],103) <= '+ Convert(Varchar(20),@Date,103)
and on Execution gave as below,
EXEC dbo.Testing '14/09/2010', 1
and got the below error
Msg 8114, Level 16, State 5, Procedure Testing, Line 0
Error converting data type varchar to datetime.
May the issue being with the date field??
August 10, 2010 at 5:17 am
Try this , mate..
-- Conditionally drop if the procedure already exists
IF OBJECT_ID('dbo.Testing') IS NOT NULL
AND OBJECTPROPERTY(OBJECT_ID('dbo.Testing'), 'IsProcedure') = 1
BEGIN
PRINT 'Dropping Stored procedure Testing...'
DROP PROCEDURE dbo.Testing
END ;
GO
-- Procedure Definition
CREATE PROCEDURE dbo.Testing
@Date DATETIME ,
@Col INT
AS
BEGIN
-- Declaration of Local variables
DECLARE @SQL VARCHAR(MAX)
DECLARE @grp1 VARCHAR(1000)
DECLARE @grp2 VARCHAR(1000)
--DECLARE @Col VARCHAR(10)
DECLARE @SELECTLIST VARCHAR(MAX)
-- Initiation of Vairables
-- SET @Col= 2
SET @grp1= '[Tab1SalesHeader].No, [Tab1SalesLine].Description, [Tab1SalesLine].[Variant Code]'
SET @grp2= '[Tab1SalesHeader].No, [Tab1Sales Line].Description'
-- conditionally check trhe input
IF (@Col=1)
SET @SELECTLIST = @grp1
ELSE
SET @SELECTLIST = @grp2
SET @SQL = 'Select ' + @SELECTLIST + '
, Convert(numeric(10,2)
, SUM([Tab1SalesLine].Quantity)) AS EXPR1
, Count(*) Row_Count
FROM [Tab1SalesHeader]
INNER JOIN [Tab1SalesLine]
ON [Tab1SalesHeader].No = [Tab1SalesLine].[No]
WHERE [Tab1SalesHeader].OrderDate < CAST ( '''+ CAST( @Date AS VARCHAR ) + ''' AS DATETIME)' +
' GROUP BY ' + @SELECTLIST
EXEC(@SQL)
END ;
GO
-- Check if the procedure is created sucessfully
IF OBJECT_ID('dbo.Testing') IS NOT NULL
AND OBJECTPROPERTY(OBJECT_ID('dbo.Testing'), 'IsProcedure') = 1
BEGIN
PRINT 'Create Stored procedure Testing Successfully'
END ;
GO
My bad, in my earlier procedure, i totally missed out that i will have to add apostrophes for the dates.. please try the above code and get back to us if it works 🙂
August 10, 2010 at 5:46 am
Wow !! Really very Happy !! Thanks a Lot ColdCoffee !! U Rocking txtPost_CommentEmoticon(':-)');
It works .. hooooo...
Wondering tht the task become possible from impossible (As i started this thread so).. Really im very THankfull to all who have assisted me..
Thanks all!!
If there exists options to assign point let me know ..
I am giving Full marks !!
August 10, 2010 at 6:13 am
New Commer (8/10/2010)
Wow !! Really very Happy !! Thanks a Lot ColdCoffee !! U Rocking txtPost_CommentEmoticon(':-)');It works .. hooooo...
Wondering tht the task become possible from impossible (As i started this thread so).. Really im very THankfull to all who have assisted me..
Thanks all!!
If there exists options to assign point let me know ..
I am giving Full marks !!
Cool.. glad i could be of help to you! 🙂
i just did the beautification and added some missed out things.. Toss your thanks to Mr.rvasanth who wrapped things up into a SP.. 😎
August 10, 2010 at 6:21 am
Of Course i have Too !!
Thanks Mr. Vasanth !! :O)
August 10, 2010 at 6:23 am
rvasanth (8/9/2010)
Create Procedure Testing(
@Col int
)
AS
DECLARE @SQL VARCHAR(MAX)
DECLARE @grp1 VARCHAR(1000)
DECLARE @grp2 VARCHAR(1000)
//DECLARE @Col VARCHAR(10)
DECLARE @SELECTLIST VARCHAR(MAX)
//SET @Col= 2
SET @grp1= '[Tab1SalesHeader].No, [Tab1SalesLine].Description, [Tab1SalesLine].[Variant Code]'
SET @grp2= '[Tab1SalesHeader].No, [Tab1Sales Line].Description'
IF (@Col=1)
SET @SELECTLIST = @grp1
ELSE
SET @SELECTLIST = @grp2
SET @SQL = 'Select ' +@SELECTLIST +'
, Convert(numeric(10,2), SUM([Tab1SalesLine].Quantity)) AS EXPR1,Count(*) From
[Tab1SalesHeader] INNER JOIN [Tab1SalesLine] ON [Tab1SalesHeader].No = [Tab1SalesLine].[No]' +
'Group By ' + @SELECTLIST
EXEC(@SQL)
Thanks for being a stepping stone to my task !! Thanks :O)
Viewing 7 posts - 16 through 21 (of 21 total)
You must be logged in to reply to this topic. Login to reply