EXCEL: parameters and stored procedures

Hi,
I'm trying to run a sproc that was created on SQL server and has two date
parameters. I can run the sproc easily enough with fixed values but it
doesn't work with parameters as the sproc can't be displayed graphically?!?!
I have seen that the suggested solution is to create a view but as this
query uses group by, using the where clause in a view to specify the
parameteres will not give the correct result set.
This is part of the SQL from the sproc.....
SELECT SG.SchemeName as SchemeName,
'Fail' as Type,
COUNT(CASE WHEN Q.Createdby = 'WEBUSER' THEN 1 END) as Web,
COALESCE(SUM(CASE WHEN Q.Createdby = 'WEBUSER') THEN Q.GrossPremium END),0)
as Value
FROM Quote Q
INNER JOIN SchemeGroup SG
ON Q.SchemeGroupID = SG.ID
INNER JOIN SubScheme SS
ON Q.ID = SS.QuoteID
WHERE lql.dbo.isPolicy(Q.ID) = 0
AND Q.CreatedAt BETWEEN @.SDATE AND @.FDATE
GROUP BY SG.SchemeName
ORDER BY SG.SchemeName,Type desc
Any help or ideas would be appreciated.
Thanks,
RuzRuz
CREATE PROC myProc
@.SDate DATETIME,
@.FDate DATETIME
AS
--Your code here
If not sure I understand you
What does it mean?
> doesn't work with parameters as the sproc can't be displayed
graphically?!?!
"Ruz" <Ruz@.discussions.microsoft.com> wrote in message
news:704B8274-534E-4C83-906B-867245C0ED8E@.microsoft.com...
> Hi,
> I'm trying to run a sproc that was created on SQL server and has two date
> parameters. I can run the sproc easily enough with fixed values but it
> doesn't work with parameters as the sproc can't be displayed
graphically?!?!
> I have seen that the suggested solution is to create a view but as this
> query uses group by, using the where clause in a view to specify the
> parameteres will not give the correct result set.
> This is part of the SQL from the sproc.....
> SELECT SG.SchemeName as SchemeName,
> 'Fail' as Type,
> COUNT(CASE WHEN Q.Createdby = 'WEBUSER' THEN 1 END) as Web,
> COALESCE(SUM(CASE WHEN Q.Createdby = 'WEBUSER') THEN Q.GrossPremium
END),0)
> as Value
> FROM Quote Q
> INNER JOIN SchemeGroup SG
> ON Q.SchemeGroupID = SG.ID
> INNER JOIN SubScheme SS
> ON Q.ID = SS.QuoteID
> WHERE lql.dbo.isPolicy(Q.ID) = 0
> AND Q.CreatedAt BETWEEN @.SDATE AND @.FDATE
> GROUP BY SG.SchemeName
> ORDER BY SG.SchemeName,Type desc
> Any help or ideas would be appreciated.
> Thanks,
> Ruz
>|||Hi Uri,
I think I might have mislead you slightly. My sproc works fine there is no
problem with that. The problem is when I am trying to call it from Microsoft
Query within Excel. The users had requested a spreadsheet where they can
update the data from the live database, this works fine with view and indeed
my sproc when fixed values are used but not when i try to add parameters and
this is (from the error message) because Microsoft query can't display this
graphically.
If I put this in the SQL window in Excel it will work:
exec RAC_ProcName '1 mar 2005','30 Mar 2005'
(Excel states that parameter should be represented by ?)
So it should work if it do this:
exec RAC_ProcName ?,?
But I get the error message:
"parameters are not allowed in queries that cannot be displayed graphically"
Cheers,
"Uri Dimant" wrote:

> Ruz
> CREATE PROC myProc
> @.SDate DATETIME,
> @.FDate DATETIME
> AS
> --Your code here
>
> If not sure I understand you
> What does it mean?
> graphically?!?!
>
> "Ruz" <Ruz@.discussions.microsoft.com> wrote in message
> news:704B8274-534E-4C83-906B-867245C0ED8E@.microsoft.com...
> graphically?!?!
> END),0)
>
>|||Ruz
I have no experience of how to call it from EXCEL but looking at the error
message it seems that you cannot do that.
I used to get the data from Excel
SELECT *
FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
'Data Source="c:\MyExcel.xls";
User ID=Admin;Password=;Extended properties=Excel 8.0')...Book1$
"Ruz" <Ruz@.discussions.microsoft.com> wrote in message
news:02B7C405-5361-4137-8690-5B5EFC116B75@.microsoft.com...
> Hi Uri,
> I think I might have mislead you slightly. My sproc works fine there is no
> problem with that. The problem is when I am trying to call it from
Microsoft
> Query within Excel. The users had requested a spreadsheet where they can
> update the data from the live database, this works fine with view and
indeed
> my sproc when fixed values are used but not when i try to add parameters
and
> this is (from the error message) because Microsoft query can't display
this
> graphically.
> If I put this in the SQL window in Excel it will work:
> exec RAC_ProcName '1 mar 2005','30 Mar 2005'
> (Excel states that parameter should be represented by ?)
> So it should work if it do this:
> exec RAC_ProcName ?,?
> But I get the error message:
> "parameters are not allowed in queries that cannot be displayed
graphically"[vbcol=seagreen]
>
> Cheers,
> "Uri Dimant" wrote:
>
date[vbcol=seagreen]
this[vbcol=seagreen]