Conversion error when calling stored procedure

Hi all,
I want to execute a stored procedure from a report. The stored procedure
takes nvarchar(50) parameters. When I call the procedure from a report with
string paramerers, I get the error 'Implicit conversion from data type
sql_variant to varchar is not allowed'. But, I cannot Cast or Convert my
reporting services parameters when calling Exec to run the stored procedure.
Help! Anyone experience this kind of problem before? Any suggestions are
welcome!hey.
Not sure why it happens as I get those all the time to. I have found that
if i create a make the first dataset something simple, like the query to
populate a parameter drop down list, then add a second data source I can then
call the execute statement for the proc. Boqus I know but it works. Buggy
software is my quess, I think it may have been the service pack as I did not
do this a few months back.
hth
"Bas" wrote:
> Hi all,
> I want to execute a stored procedure from a report. The stored procedure
> takes nvarchar(50) parameters. When I call the procedure from a report with
> string paramerers, I get the error 'Implicit conversion from data type
> sql_variant to varchar is not allowed'. But, I cannot Cast or Convert my
> reporting services parameters when calling Exec to run the stored procedure.
> Help! Anyone experience this kind of problem before? Any suggestions are
> welcome!
>

Control Result of ExecuteScalar

The user is calling a Stored Procedure with ExecuteScalar. When the SQL doesn't find a match, I'd like to return the results of a different SQL.

For Example:

If this doesn't find a match:

select amount from Lookup Where application = @.app

Then I'd like to return:

select amount from Lookup Where application = "DEFAULT"

My actual situation is more complex than this. The first SQL is in a CASE statement. After my CASE is done, can I check the current ExecuteScalar return value? Or someone determine how many records are in the last SQL to execute?

Also, ExecuteScalar always seems to get the 1st column of the 1st query. Can I have it get the 1st column from the 3rd query?

ExecuteScalar returns only one value -what that value is depends upon the stored procedure.

Yes, you can return any one value from any combination of queries.

To best assist you, please post the entire stored procedure, a description of what results are desired.

|||

I'm including my SP below. The Lookup table has a column named amount. If the CASE statement, when method="LIST", I'd like to return Lookup.amount if there are no matching records in the LookupList table.

USE [SharedDB]

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

ALTER PROCEDURE [dbo].[PR_LookupPrice]

(

@.app varchar(8),

@.billcode varchar(4),

@.code varchar(20),

@.value sql_variant

)

AS

SET NOCOUNT OFF;

SELECT CASE

WHEN method = 'SET'

THEN (select amount from Lookup Where application = @.app AND billcode = @.billcode AND code = @.code)

WHEN method = 'VAR'

THEN (Select LookupVariable.amount from Lookup INNER JOIN LookupVariable

On Lookup.lookupid = LookupVariable.lookupid

Where application = @.app AND billcode = @.billcode AND code = @.code and low <= @.value and high >= @.value)

WHEN method = 'COMP'

THEN (select SUM(LookupCompounding.amount) from Lookup INNER JOIN LookupCompounding

On Lookup.lookupid = LookupCompounding.lookupid

Where application = @.app AND billcode = @.billcode AND code = @.code)

WHEN method = 'LIST'

THEN (select LookupList.amount from Lookup INNER JOIN LookupList

On Lookup.lookupid = LookupList.lookupid

Where application = @.app AND billcode = @.billcode AND code = @.code and lookuplist.value = @.value)

END AS 'result'

FROM Lookup WHERE application = @.app AND billcode = @.billcode AND code = @.code

|||

Thanks, that helps.

I've revised the procedure for readibiltiy.

Code Snippet

ALTER PROCEDURE [dbo].[PR_LookupPrice]
( @.App varchar(8),
@.BillCode varchar(4),
@.Code varchar(20),
@.Value sql_variant
)
AS

BEGIN

SET NOCOUNT OFF;

DECLARE @.ReturnValue decimal(10,2)

SELECT @.ReturnValue = CASE Method
WHEN 'SET'
THEN Amount
WHEN 'VAR'
THEN (SELECT lv.Amount
FROM Lookup l
INNER JOIN LookupVariable lv
ON l.LookupID = lv.LookupID
WHERE ( l.Application = @.App
AND l.BillCode = @.BillCode
AND l.Code = @.Code
AND ( lv.Low <= @.Value
AND lv.High >= @.Value
)
)
)
WHEN 'COMP'
THEN (SELECT sum( lc.Amount )
FROM Lookup l
INNER JOIN LookupCompounding lc
ON l.LookupID = lc.LookupID
WHERE ( l.Application = @.App
AND l.BillCode = @.BillCode
AND l.Code = @.Code
)
)
WHEN 'LIST'
THEN (SELECT isnull( ll.Amount, l.Amount )
FROM Lookup l
INNER JOIN LookupList ll
ON l.LookupID = ll.LookupID
WHERE ( l.Application = @.App
AND l.BillCode = @.BillCode
AND l.Code = @.Code
AND ll.Value = @.Value
)
)
END
FROM Lookup
WHERE ( Application = @.App
AND BillCode = @.BillCode
AND Code = @.Code
)

IF @.ReturnValue IS NULL
SELECT Amount
FROM Lookup
WHERE Application = "DEFAULT"

RETURN @.ReturnValue

END

The changes are in yellow, I think that everything else is just formatting.

Consuming results sets in a calling SQL procedure

Dear All,

This is a query surrounding a problem I encountered
yesterday.

In SQL Server, it is possible to write a procedure that
has one or more select statements in it.

The results from these select statements will all be
individually returned to SQL Query Analyser where they
can be viewed in "grid" views. Also, these individual
results sets can be consumed by eg ADO.NET by stepping
through each results set in turn and processing the
respective results.

My question is, can you do the same in a SQL Server
procedure? ie:

Create Procedure Proc1
AS
begin
select Col1, COl2
from Table1

select Col1, Col2, Col3
from Table2
end

Create Procedure Proc2
AS
begin
exec Proc1
end

Can both/either of the results sets from Proc1 be
consumed by the calling procedure Proc2?

I can see that you could design the procedures up-
front to do almost anything without consuming the
result sets in this way, but if the procedures
returning the results sets are already built and
in use in other places (for instance in client code),
can they be re-used on server-side SQL procedures?
Thanks in anticipation!

Paul.In the example you have, the result to the caller of Proc 2 will see
the two results of the Proc1 procedure. As far as Proc2 "consuming" the
results of Proc1, there is no operation happening on the results in
your example.

Are you asking if you could return multiple tables from another proc
and do some manipulation on them, before you return those to the caller?|||Thanks for the quick reply!

> As far as Proc2 "consuming" the results of Proc1,
> there is no operation happening on the results
> in your example.

That is because I don't know how to represent it at
the moment - hopefully that's where you come in! ;-)

> Are you asking if you could return multiple tables
> from another proc and do some manipulation on them,
> before you return those to the caller?

yes - whether I can return and manipulate one or more
data sets into a calling procedure, *without* changing my
original (called) procedures which take a form similar to:

Create Procedure Proc1
AS
begin
select Col1, COl2
from Table1

select Col1, Col2, Col3
from Table2
end

I hope this makes sense...

Thanks!

Paul.|||You can use the NextResult method of a SqlDataReader object to process
multiple result sets returned from a single command. For example:

SqlDataReader myDataReader = myCommand.ExecuteReader();
while(true)
{
while(myDataReader.Read())
{
ProcessMyResults();
}
if(!myDataReader.NextResult()) break;
}
myDataReader.Close();

--
Hope this helps.

Dan Guzman
SQL Server MVP

<p_le_sueur_1@.hotmail.com> wrote in message
news:1102508250.805756.307860@.z14g2000cwz.googlegr oups.com...
> Thanks for the quick reply!
>> As far as Proc2 "consuming" the results of Proc1,
>> there is no operation happening on the results
>> in your example.
> That is because I don't know how to represent it at
> the moment - hopefully that's where you come in! ;-)
>> Are you asking if you could return multiple tables
>> from another proc and do some manipulation on them,
>> before you return those to the caller?
> yes - whether I can return and manipulate one or more
> data sets into a calling procedure, *without* changing my
> original (called) procedures which take a form similar to:
> Create Procedure Proc1
> AS
> begin
> select Col1, COl2
> from Table1
> select Col1, Col2, Col3
> from Table2
> end
> I hope this makes sense...
> Thanks!
> Paul.|||On 8 Dec 2004 04:17:30 -0800, p_le_sueur_1@.hotmail.com wrote:

> Thanks for the quick reply!
>> As far as Proc2 "consuming" the results of Proc1,
>> there is no operation happening on the results
>> in your example.
> That is because I don't know how to represent it at
> the moment - hopefully that's where you come in! ;-)
>> Are you asking if you could return multiple tables
>> from another proc and do some manipulation on them,
>> before you return those to the caller?
> yes - whether I can return and manipulate one or more
> data sets into a calling procedure, *without* changing my
> original (called) procedures which take a form similar to:
> Create Procedure Proc1
> AS
> begin
> select Col1, COl2
> from Table1
> select Col1, Col2, Col3
> from Table2
> end
> I hope this makes sense...
> Thanks!
> Paul.

The INSERT INTO ... EXEC command can let a T-SQL batch (or procedure)
consume ONE resultset from another procedure, but not multiple resultsets.
Sorry.

Dan's method shows how to consume multiple resultsets from a .NET client,
but not from another stored procedure.|||(p_le_sueur_1@.hotmail.com) writes:
> My question is, can you do the same in a SQL Server
> procedure? ie:
> Create Procedure Proc1
> AS
> begin
> select Col1, COl2
> from Table1
> select Col1, Col2, Col3
> from Table2
> end
> Create Procedure Proc2
> AS
> begin
> exec Proc1
> end
> Can both/either of the results sets from Proc1 be
> consumed by the calling procedure Proc2?

When you call Proc2, the result sets go to the client. You can use
INSERT/EXEC to catch the data, but it only works if the result sets
are equally structures. (I think it works then, I am not sure.)

Anyway, I have an article on my web site, which discusses this in detail:
http://www.sommarskog.se/share_data.html.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp