July 25, 2011 at 12:39 am
Hi frd's,
I want to know how 2 pass an @Source as an peremeter for the following query
Tables company c , out_let ol and temp table #Stationary
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[p_Stationary_test1]
-- Add the parameters for the stored procedure here
@Reporting_DATE DATETIME = NULL,
@source nvarchar
AS
BEGIN
SET @Reporting_DATE = GETDATE()
CREATE TABLE #Stationary (
[source] NVARCHAR(20)
, entity NVARCHAR(8)
, currency NVARCHAR(3)
, start_validity_date DATETIME
, end_validity_date DATETIME
, total_books NUMERIC(38,8)
)
INSERT INTO #Stationary ( [source],entity,currency,start_validity_date,end_validity_date )
SELECT ol.[source]
, ol.entity
, ol.currency
, ol.start_validity_date
, ol.end_validity_date
FROM outlet ol
GROUP BY ol.[source],ol.entity,ol.currency,ol.start_validity_date,ol.end_validity_date
UPDATE #Stationary
SET total_books = tb.total_books
FROM #Stationary st, (SELECT ol.[source],ol.entity,ol.currency,
SUM (c.number_books) -- data type Numeric (38,8)
AS total_books FROM outlet ol
INNER JOIN company c
ON st.entity=ol.entity
GROUP BY st.[source],st.entity,st.currency)tb
WHERE @Reporting_DATE BETWEEN gl.start_validity_date and gl.end_validity_date
and tb.currency = st.currency
SELECT [source],entity,currency,total_books FROM #Stationary
Adv tkq
Need help? Help us help you.
July 25, 2011 at 10:03 am
What exactly are you trying to do? Are you wanting to pass a different set of parameters to an existing stored proc?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
July 25, 2011 at 10:16 am
If you call the stored procedure, you do it like this
exec [dbo].[p_Stationary_test1] @MyDate, @MySource
Where you've declared the date and character variables as the appropriate types and assigned values.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply