March 16, 2009 at 10:35 am
Ok.
But you still haven't post the exact script that you are running...
We can't help if we don't know the values of the variables
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
March 16, 2009 at 10:39 am
this is the query I'm running: its the sameone from my first post.
Declare @SQL nvarchar(MAX)
set @SQL = ' SELECT x.SaleYear, x.TotalSales
FROM (SELECT SaleYear,
TotalSales= ISNULL((SELECT SUM(TotalSales) FROM InsSales WHERE region= ''NE'' AND SaleYear = y.SaleYear and salesPerson in (''' + @SalesPerson + ''') and SalesMonth <= (' + @Salesmonth + ') ,0)
FROM InsSales y where SaleYear in (' + @CYear + ', ' + @PYear + ')
GROUP BY SaleYear) As x '
March 16, 2009 at 10:40 am
where is the code that sets the variables and declares the variables???
I need a script I can copy directly from here and paste into a SSMS window and run to replicate what you doing...
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
March 16, 2009 at 10:45 am
the variables are being passed from a .net web app, but when the proc is called it looks like this when i ran SQL Profile:
exec sp_GetSalesData @CurrentYear=N'2009',@PrevYear=N'2008',@Salesmonth=2, @SalesPerson='Smith'
March 16, 2009 at 10:59 am
cool please could you post the full CREATE PROCEDURE code.
Sorry to be a pain, but something is missing here...
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
March 16, 2009 at 11:02 am
yeah, I'll post it in a few. This thing is driving me nuts because I have another proc in another DB and the proc is setup the sameway and works,
March 16, 2009 at 11:06 am
cool I'll wait for your reply.
It might also be funny data in your table.
but I can't see any columns being used for the dynamic build...
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
March 16, 2009 at 11:09 am
here is the create statement
CREATE PROCEDURE dbo.sp_GetSalesData
@CurrentYear nvarchar(4),
@PrevYear nvarchar(4),
@Month nvarchar(2),
@SalesMan varchar(max)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
DECLARE @CYear VARCHAR(100)
DECLARE @PYear VARCHAR(100)
DECLARE @SalesPerson VARCHAR(100)
DECLARE @Salesmonth VARCHAR(100)
SELECT
@CYear = @CurrentYear
@PYear = @PrevYear
@SalesPerson = @SalesMan
@Salesmonth = @Month
Declare @SQL nvarchar(MAX)
set @SQL = ' SELECT x.SaleYear, x.TotalSales
FROM (SELECT SaleYear,
TotalSales= ISNULL((SELECT SUM(TotalSales) FROM InsSales
WHERE region= ''NE'' AND SaleYear = y.SaleYear and salesPerson in (''' + @SalesPerson + ''')
and SalesMonth <= (' + @Salesmonth + ')) ,0)
FROM InsSales y where SaleYear in (' + @CYear + ', ' + @PYear + ')
GROUP BY SaleYear) As x '
Exec @SQL
END
GO
as for the actual query, another version of it work, but I'm trying to simplify it, so I can make it more "dynamic"
March 16, 2009 at 11:20 am
Sorry was thinking of something else.
If you change the EXEC @SQL to :
EXEC (@SQL)
It should work
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
March 16, 2009 at 12:37 pm
ARG! !
now when I do that, and try to execute the sp, i just get this {Command(s) completed successfully.} and no data is returned.
This thing is driving me to drink:P
March 16, 2009 at 12:49 pm
INSANE, I got it somewhat working but its returning me [ 0]'s, but its running and returning me something. thanks for your help.
March 17, 2009 at 3:16 am
at least you not getting an error now.
So all that needs to be is to make sure your data is correct that you searching for 🙂
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
March 18, 2009 at 9:25 am
Here you are!!
set @SQL = ' SELECT x.SaleYear, x.TotalSales
FROM (SELECT SaleYear,
TotalSales= ISNULL((SELECT SUM(TotalSales)
FROM InsSales
WHERE region= ''NE''
AND SaleYear = y.SaleYear and salesPerson in (''' + @SalesPerson + ''') and SalesMonth <= ' + CONVERT(NVARCHAR(20), @Salesmonth) + ') ,0)
FROM InsSales y where SaleYear in (' + CONVERT(NVARCHAR(20), @CYear) + ', ' + CONVERT(NVARCHAR(20), @PYear) + ')
GROUP BY SaleYear) As x '
The error was a surplus ( at SalesMonth <= ('
Very neat!
March 18, 2009 at 9:28 am
Ooops, think I hit the wrong button, this post might be duplicated.
Here is your solution:-
set @SQL = ' SELECT x.SaleYear, x.TotalSales
FROM (SELECT SaleYear,
TotalSales= ISNULL((SELECT SUM(TotalSales)
FROM InsSales
WHERE region= ''NE''
AND SaleYear = y.SaleYear and salesPerson in (''' + @SalesPerson + ''') and SalesMonth <= ' + CONVERT(NVARCHAR(20), @Salesmonth) + ') ,0)
FROM InsSales y where SaleYear in (' + CONVERT(NVARCHAR(20), @CYear) + ', ' + CONVERT(NVARCHAR(20), @PYear) + ')
GROUP BY SaleYear) As x '
You had a surplus bracket ( at SalesMonth <= ('
March 20, 2009 at 1:08 am
if the Error message is:
The name ' SELECT x.SaleYear, x.TotalSales
FROM (SELECT SaleYear,
TotalSales= ISNULL((SELECT SUM(TotalSales) FROM InsSales
WHERE region= 'NE' AND SaleYear = y.SaleYear and salesPerson in ('Smith')
and SalesMonth <= (2)) ,0)
FROM InsSales y where SaleYear in (2009, 2008)
GROUP BY SaleYear) As x ' is not a valid identifier
Use
Exec (@SQL)
instead of
Exec @SQL
Viewing 15 posts - 16 through 30 (of 32 total)
You must be logged in to reply to this topic. Login to reply