Return COMPUTE from stored proc

I'm trying to figure out how to get my stored proc below to just return the result for COMPUTE only:
ALTER PROCEDURE [dbo].[procname]
AS
BEGIN
Select (cast(FGoal as numeric(30,2)) / FSched) * 100 AS gt
from DR WHERE e='06'
group by CustomerName,
CustomerNumber,
FGoal,
FSched
order by CustomerNumber
COMPUTE SUM((cast(FGoal as numeric(30,2)) / FSched) * 100)
END
When my stored proc is run, it should only return one value, the result of COMPUTE SUM((cast(FGoal as numeric(30,2)) / FSched) * 100). Right now however, it returns only the list from the select (below) which I don't want, I just want the COMPUTE value returned which is one value (the sum of the items below):
27256.000000
14218.000000
0.000000
14930.000000
54824.000000
148616.666667
73320.000000
85956.000000
105507.500000
67911.904762
55276.190476
14467.500000
5985.000000
20910.000000
118784.000000
5340.000000
5295.000000
567.500000

It is not possible to modify behavior of COMPUTE. It is a non-standard / proprietary extension so you should avoid using it. Instead write your own query using say ROLLUP/CUBE operator to get the sum at the desired levels. This will allow you to control the output rows by filtering on GROUPING function return values. See BOL for examples.|||

Thanks figured that after spending half my day trying. I found a way to sum what I needed and return one GT without compute:

Select Total = SUM(gt)

FROM

(Select DistinctCustomerName,

CustomerNumber,

FeeGoal_AZ AS FG,

FeeSchedule,

(cast(FeeGoal_AZ as numeric(30,10)) / FeeSchedule) * 100 AS gt

from DCR WHERE branch='00002'

group by CustomerName,

CustomerNumber,

FeeGoal_AZ,

FeeSchedule

) as dTable

Return Code Not Capturing an Alter Database Failure

I'm trying to apply the following code into a proc I have and wanted to check
for the success of the alter database stmt. @.@.error didn't trip to <> 0 and
then tried placing a return status code variable after the execute stmt and
received a syntax error.
An excerpt of the code follows :
OPEN DB2Defrag
FETCH NEXT FROM DB2Defrag INTO @.DBNames
WHILE @.@.FETCH_STATUS = 0
BEGIN
set @.DBNames = '[' + @.DBNames + ']'
select @.cmdstr = 'alter database ' + @.DBNames + ' set recovery
bulk_logged'
print @.cmdstr
exec @.ret_code= (@.cmdstr)
-- if @.@.error <> 0
if @.ret_code <> 0
Any ideas? thks
tom.frost@.ge.comTry using sp_executesql instead.
exec @.ret_code = sp_executesql @.cmdstr
set @.err = coalesce(nullif(@.ret_code, 0), @.@.error)
if @.error != 0
...
Implementing Error Handling with Stored Procedures
http://www.sommarskog.se/error-handling-II.html#dynamic-sql
Error Handling in SQL Server â' a Background
http://www.sommarskog.se/error-handling-I.html
AMB
"tom frost" wrote:
> I'm trying to apply the following code into a proc I have and wanted to check
> for the success of the alter database stmt. @.@.error didn't trip to <> 0 and
> then tried placing a return status code variable after the execute stmt and
> received a syntax error.
> An excerpt of the code follows :
> OPEN DB2Defrag
> FETCH NEXT FROM DB2Defrag INTO @.DBNames
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> set @.DBNames = '[' + @.DBNames + ']'
> select @.cmdstr = 'alter database ' + @.DBNames + ' set recovery
> bulk_logged'
> print @.cmdstr
> exec @.ret_code= (@.cmdstr)
> -- if @.@.error <> 0
> if @.ret_code <> 0
> Any ideas? thks
> tom.frost@.ge.com

Return Code Not Capturing an Alter Database Failure

I'm trying to apply the following code into a proc I have and wanted to chec
k
for the success of the alter database stmt. @.@.error didn't trip to <> 0 an
d
then tried placing a return status code variable after the execute stmt and
received a syntax error.
An excerpt of the code follows :
OPEN DB2Defrag
FETCH NEXT FROM DB2Defrag INTO @.DBNames
WHILE @.@.FETCH_STATUS = 0
BEGIN
set @.DBNames = '[' + @.DBNames + ']'
select @.cmdstr = 'alter database ' + @.DBNames + ' set recovery
bulk_logged'
print @.cmdstr
exec @.ret_code= (@.cmdstr)
-- if @.@.error <> 0
if @.ret_code <> 0
Any ideas? thks
tom.frost@.ge.comTry using sp_executesql instead.
exec @.ret_code = sp_executesql @.cmdstr
set @.err = coalesce(nullif(@.ret_code, 0), @.@.error)
if @.error != 0
...
Implementing Error Handling with Stored Procedures
http://www.sommarskog.se/error-hand...tml#dynamic-sql
Error Handling in SQL Server – a Background
http://www.sommarskog.se/error-handling-I.html
AMB
"tom frost" wrote:

> I'm trying to apply the following code into a proc I have and wanted to ch
eck
> for the success of the alter database stmt. @.@.error didn't trip to <> 0
and
> then tried placing a return status code variable after the execute stmt an
d
> received a syntax error.
> An excerpt of the code follows :
> OPEN DB2Defrag
> FETCH NEXT FROM DB2Defrag INTO @.DBNames
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> set @.DBNames = '[' + @.DBNames + ']'
> select @.cmdstr = 'alter database ' + @.DBNames + ' set recovery
> bulk_logged'
> print @.cmdstr
> exec @.ret_code= (@.cmdstr)
> -- if @.@.error <> 0
> if @.ret_code <> 0
> Any ideas? thks
> tom.frost@.ge.com

Return Code Not Capturing an Alter Database Failure

I'm trying to apply the following code into a proc I have and wanted to check
for the success of the alter database stmt. @.@.error didn't trip to <> 0 and
then tried placing a return status code variable after the execute stmt and
received a syntax error.
An excerpt of the code follows :
OPEN DB2Defrag
FETCH NEXT FROM DB2Defrag INTO @.DBNames
WHILE @.@.FETCH_STATUS = 0
BEGIN
set @.DBNames = '[' + @.DBNames + ']'
select @.cmdstr = 'alter database ' + @.DBNames + ' set recovery
bulk_logged'
print @.cmdstr
exec @.ret_code= (@.cmdstr)
-- if @.@.error <> 0
if @.ret_code <> 0
Any ideas? thks
tom.frost@.ge.com
Try using sp_executesql instead.
exec @.ret_code = sp_executesql @.cmdstr
set @.err = coalesce(nullif(@.ret_code, 0), @.@.error)
if @.error != 0
...
Implementing Error Handling with Stored Procedures
http://www.sommarskog.se/error-handl...ml#dynamic-sql
Error Handling in SQL Server – a Background
http://www.sommarskog.se/error-handling-I.html
AMB
"tom frost" wrote:

> I'm trying to apply the following code into a proc I have and wanted to check
> for the success of the alter database stmt. @.@.error didn't trip to <> 0 and
> then tried placing a return status code variable after the execute stmt and
> received a syntax error.
> An excerpt of the code follows :
> OPEN DB2Defrag
> FETCH NEXT FROM DB2Defrag INTO @.DBNames
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> set @.DBNames = '[' + @.DBNames + ']'
> select @.cmdstr = 'alter database ' + @.DBNames + ' set recovery
> bulk_logged'
> print @.cmdstr
> exec @.ret_code= (@.cmdstr)
> -- if @.@.error <> 0
> if @.ret_code <> 0
> Any ideas? thks
> tom.frost@.ge.com
sql

Return Code from Stored Proc

I am doing an insert with a stored proc using the ExecuteNonQuery in the
DataAccess Block from Microsoft. My parameters are inserted correctly into
the database but my return code is always a -1 instead of 0. Please review
this code and tell me if you see something I am doing wrong> Thanks in
Advance!
CREATE PROCEDURE insertTrans_sp
(@.batch_id numeric,
@.cpi numeric,
@.visit numeric,
@.qty numeric,
@.gl_proc varchar(8),
@.gl_desc varchar(50),
@.charge float,
@.processed_date datetime,
@.processed_by varchar(50))
AS
SET NOCOUNT ON
DECLARE
@.err int,
@.err_desc varchar(50)
Begin Transaction
INSERT INTO Trans
(batch_id,
cpi,
visit,
qty,
gl_proc,
gl_desc,
charge,
processed_date,
processed_by)
VALUES
(@.batch_id,
@.cpi,
@.visit,
@.qty,
@.gl_proc,
@.gl_desc,
@.charge,
@.processed_date,
@.processed_by)
SET @.err = @.@.error
if @.err <> 0 GOTO ErrorHandler
COMMIT Transaction
return 0
ErrorHandler:
SET @.err_desc = 'Error occurred in insertTrans_sp'
EXEC insertErrLog_sp @.err, @.err_desc
return -100
GO
Robert HillRobert,
Exactly how are you executing this? Is this ADO, ADO.net etc? A couple of
comments here. One is that it is not necessary to wrap the Insert in an
explicit transaction. Any single sql statement is ATOMIC by itself. The
insert will either succeed or it won't. By wrapping it in a tran you now
have to commit or roll it back yourself. In this case you don't even have
any code to issue a rollback. You should always test the trancount level
before issuing a commit or rollback.
IF @.@.TRANCOUNT > 0
COMMIT TRAN
You declare a series of variables as Numeric but do not specify their
precision or scale. Always specify the size or scale of all data types.
These all look like Integers anyway. If that is the case it is more
efficient to declare them as integers than numeric.
Andrew J. Kelly SQL MVP
"Robert" <rhill938@.hotmail.com> wrote in message
news:0B27280E-35B3-4548-B928-34A0E227453F@.microsoft.com...
>I am doing an insert with a stored proc using the ExecuteNonQuery in the
> DataAccess Block from Microsoft. My parameters are inserted correctly
> into
> the database but my return code is always a -1 instead of 0. Please
> review
> this code and tell me if you see something I am doing wrong> Thanks in
> Advance!
> CREATE PROCEDURE insertTrans_sp
> (@.batch_id numeric,
> @.cpi numeric,
> @.visit numeric,
> @.qty numeric,
> @.gl_proc varchar(8),
> @.gl_desc varchar(50),
> @.charge float,
> @.processed_date datetime,
> @.processed_by varchar(50))
> AS
> SET NOCOUNT ON
> DECLARE
> @.err int,
> @.err_desc varchar(50)
> Begin Transaction
> INSERT INTO Trans
> (batch_id,
> cpi,
> visit,
> qty,
> gl_proc,
> gl_desc,
> charge,
> processed_date,
> processed_by)
> VALUES
> (@.batch_id,
> @.cpi,
> @.visit,
> @.qty,
> @.gl_proc,
> @.gl_desc,
> @.charge,
> @.processed_date,
> @.processed_by)
> SET @.err = @.@.error
> if @.err <> 0 GOTO ErrorHandler
> COMMIT Transaction
> return 0
> ErrorHandler:
> SET @.err_desc = 'Error occurred in insertTrans_sp'
> EXEC insertErrLog_sp @.err, @.err_desc
> return -100
> GO
> --
> Robert Hill
>|||in order to get a return code, I believe you need to use execute Scalar
Greg Jackson
PDX, Oregon|||"pdxJaxon" <GregoryAJackson@.Hotmail.com> wrote in message
news:eAujW9fHFHA.1860@.TK2MSFTNGP15.phx.gbl...
> in order to get a return code, I believe you need to use execute Scalar
>
Yes, the return from ExecuteNonQuery is NOT the stored procedure return
code.
No ExecuteScalar won't help. To get the return code you will need to use
CommandType.Text and write a batch like:
exec @.rc=MyProc(@.p1,@.p2,@.p3)
then bind an output parameter to @.rc.
In your case it's not necessary to test the return code. From client code
if something goes wrong you will get a SqlException. A stored procedure
return code is really just for other stored procedures. When one procedure
calls another procedure the calling procedure cannot intercept the error
messages generated by the called procedure, so it must use the return code
to determine if something went wrong. From SqlClient the error message will
appear as a SqlException and you can examine it in your catch block.
David|||Thanks.
"David Browne" wrote:

> "pdxJaxon" <GregoryAJackson@.Hotmail.com> wrote in message
> news:eAujW9fHFHA.1860@.TK2MSFTNGP15.phx.gbl...
> Yes, the return from ExecuteNonQuery is NOT the stored procedure return
> code.
> No ExecuteScalar won't help. To get the return code you will need to use
> CommandType.Text and write a batch like:
> exec @.rc=MyProc(@.p1,@.p2,@.p3)
> then bind an output parameter to @.rc.
> In your case it's not necessary to test the return code. From client code
> if something goes wrong you will get a SqlException. A stored procedure
> return code is really just for other stored procedures. When one procedur
e
> calls another procedure the calling procedure cannot intercept the error
> messages generated by the called procedure, so it must use the return code
> to determine if something went wrong. From SqlClient the error message wi
ll
> appear as a SqlException and you can examine it in your catch block.
> David
>
>|||Robert,
you dont have to set the command type to text.
you can (And in my opinion, should) leave the command type to "stored
procedure"
you can create a parameter object with direction of "Output" and get the
return value.
GAJ|||Thanks.
My solution was to use ExecuteScalar in the Application Block with the
command type "stored procedure" with an output parameter.
Robert
"pdxJaxon" wrote:

> Robert,
> you dont have to set the command type to text.
> you can (And in my opinion, should) leave the command type to "stored
> procedure"
> you can create a parameter object with direction of "Output" and get the
> return value.
> GAJ
>
>|||pdxJaxon wrote:
> Robert,
> you dont have to set the command type to text.
> you can (And in my opinion, should) leave the command type to "stored
> procedure"
> you can create a parameter object with direction of "Output" and get
> the return value.
>
You can also use a parameter with direction of "ReturnValue" to get the
return value ...
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"|||Thanks!
I now need to return 2 values from a table. I tested my stored proc in
query analyzer and it seemed to work fine, returning the values I need. The
foloowing is the code I use in my app to call the stored proc using a
SqlParameter array. All I get back is a -1. What am I doing wrong?
SqlParameter[] oParms = new SqlParameter[3];
try
{
oParms[0] = new SqlParameter("@.cpi", sCPI);
oParms[1] = new SqlParameter("@.visit", ParameterDirection.Output);
oParms[2] = new SqlParameter("@.batch_id", ParameterDirection.Output);
ConnectString oCn = new ConnectString();
cn = oCn.GetConnection();
object oRes = new object();
oRes = SqlHelper.ExecuteNonQuery(cn,
CommandType.StoredProcedure,
"verifyCPIandBatchId_sp",
oParms);
Robert
"Bob Barrows [MVP]" wrote:

> pdxJaxon wrote:
> You can also use a parameter with direction of "ReturnValue" to get the
> return value ...
> --
> Microsoft MVP - ASP/ASP.NET
> Please reply to the newsgroup. This email account is my spam trap so I
> don't check it very often. If you must reply off-line, then remove the
> "NO SPAM"
>
>|||close the connection before attempting to read the output parms
GAJ

Return bottom 10 rows not working.

Ok so this is more then likely very easy for most. But for me it's hard. I have a stored proc that I've been able to get to retun just the top 10 rows and have been able to order it asending. But it will only return the top 10 and I need to bottom 10 of the selection.

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO

ALTER proc [dbo].[proj_ListFiles](@.project_id int)
as
select top 10(@.project_id) id, project_id, filename, contenttype, length
from proj_file
where project_id=@.project_id

How can I make it return to bottom 10. On a side note about this, when it returns the recordset they all have the 'id' of the 'project_id' that I selected. Not that the side note really bothers me as it will not be returning the ID's to be visible.

Any help would be great,
Thanks
Tim

Hi,

To do this, you need to add an ORDER BY clause to your select statement and set it to be DESCENDING.

ALTER proc [dbo].[proj_ListFiles](@.project_id int)
as
select top 10(@.project_id) id, project_id, filename, contenttype, length
from proj_file
where project_id=@.project_id
order by project_id desc

Greetz,

Geert

Geert Verhoeven
Consultant @. Ausy Belgium

My Personal Blog

|||Hi,

in addition to Geert, be aware that TOP makes no sense without any order clause unless you want to get a set of (possible) random rows out of the database.

HTH; Jens K. Suessmeyer.

http://www.sqlserver2005.de|||

Ok I've added the order by project_id desc to the statement, but it's still only returning the same 10 rows is ascending order, it'a also changing the ID to the value I queried in this case 12. So it's sorting all of the as 12. I was looking at another and added something and it made it so my results flip around and sort ascending but are returning rows 2-11 not 1-10.

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON

GO
ALTER proc [dbo].[proj_ListFiles](@.project_id int = 0 )
as
select top 10(@.project_id) id, project_id, filename, contenttype, length
from proj_file
where project_id=@.project_id
or @.project_id = 0
order by project_id desc

Any ideas, what i'm trying to do is limit the results so that way only the last 10 entries of that same project_id will return and not all/the first 10. It will make for easier viewing for me.

By the way thanks for the help.
Tim

|||

Hi,

Since project_id is the same for all the records, you need to sort on a different field then that. For example if you want to have the 10 largest filenames, you perform an ORDER BY length DESC.

Greetz,

Geert

Geert Verhoeven
Consultant @. Ausy Belgium

My Personal Blog

|||

Well I figured it out with a little help from a co worker. Not sure if it needs it or not but I just set rowcount to 10 and removed the (@.project_id) from the top 10 command and it works like a dream (a really weird nerdy dream).

ALTER proc proj_ListFiles(@.project_id int)

as

set rowcount 10

select top 10 id, project_id, filename, contenttype, length

from proj_file

where project_id=@.project_id

order by id desc

Thanks for the help.

Tim

|||

The procedure mentioned above was wrong, it took the projectId as a static expression leading to the fact that it produced 10 time the number you put in as project id, e.g.

SELECT TOP 10(10) From sys.sysobjects

10
10
10
10
10
10
10
10
10
10

YOu will not have to set SET ROWCOUNT, just use the Top with either the syntax

select top 10 id, project_id, filename, contenttype, length

from proj_file

where project_id=@.project_id

order by id desc

(Which is only supported by SQL Server 2005 as backward compatibility)

or

select top (10) id, project_id, filename, contenttype, length

from proj_file

where project_id=@.project_id

order by id desc

Which is new in SQL Server 2005.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||

Thank you very much for this reply, and yes you are correct I didn't have to use 'set rowcount 10' it was removed within minutes of my previous post. Another coworker that had helped me on it yesterday showed me that you did not have to use it. So the second I got home (10 minutes ago) I updated the stored proc and it worked perfect. Now I just hope the rest of them go easier than that.

Thanks again every one.
Tim

return big string

Hello,
How can I return more then 8000 characters from a store proc.?
When I do this
CREATE PROCEDURE SP_GetClassCode
AS
SELECT 'very long string'
GO
It work's
But when I do this
CREATE PROCEDURE SP_GetClassCode
@.Replace AS VARCHAR(50)
AS
SELECT 'very long ' + @.Replace
GO
This time it only return the 8000 first characters
What do I have to do to return more then 8000 characters from a Store Proc?
Thank you
Marc R.Marc Robitaille wrote:
> Hello,
> How can I return more then 8000 characters from a store proc.?
> When I do this
> CREATE PROCEDURE SP_GetClassCode
> AS
> SELECT 'very long string'
> GO
> It work's
> But when I do this
> CREATE PROCEDURE SP_GetClassCode
> @.Replace AS VARCHAR(50)
> AS
> SELECT 'very long ' + @.Replace
> GO
> This time it only return the 8000 first characters
> What do I have to do to return more then 8000 characters from a Store
> Proc?
> Thank you
> Marc R.
You could return the values as two or more columns and concatenate on
the client. The data type you are working with (char/varchar) is limited
to 8000 bytes.
--
David Gugick
Quest Software
www.imceda.com
www.quest.com|||Is there an other datatype that I could use to return what I need?
"David Gugick" <david.gugick-nospam@.quest.com> a écrit dans le message de
news: %235Wco9uhFHA.3256@.TK2MSFTNGP12.phx.gbl...
> Marc Robitaille wrote:
>> Hello,
>> How can I return more then 8000 characters from a store proc.?
>> When I do this
>> CREATE PROCEDURE SP_GetClassCode
>> AS
>> SELECT 'very long string'
>> GO
>> It work's
>> But when I do this
>> CREATE PROCEDURE SP_GetClassCode
>> @.Replace AS VARCHAR(50)
>> AS
>> SELECT 'very long ' + @.Replace
>> GO
>> This time it only return the 8000 first characters
>> What do I have to do to return more then 8000 characters from a Store
>> Proc?
>> Thank you
>> Marc R.
> You could return the values as two or more columns and concatenate on the
> client. The data type you are working with (char/varchar) is limited to
> 8000 bytes.
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com|||How are you determining the length returned? Query Analyzer has a limit of
8000 bytes per column. And you should avoid using sp_ as a prefix to stored
procedures.
--
Andrew J. Kelly SQL MVP
"Marc Robitaille" <marc.robitaille@.ars-solutions.caa> wrote in message
news:%23i2uleuhFHA.1464@.TK2MSFTNGP14.phx.gbl...
> Hello,
> How can I return more then 8000 characters from a store proc.?
> When I do this
> CREATE PROCEDURE SP_GetClassCode
> AS
> SELECT 'very long string'
> GO
> It work's
> But when I do this
> CREATE PROCEDURE SP_GetClassCode
> @.Replace AS VARCHAR(50)
> AS
> SELECT 'very long ' + @.Replace
> GO
> This time it only return the 8000 first characters
> What do I have to do to return more then 8000 characters from a Store
> Proc?
> Thank you
> Marc R.
>|||I try to build a VB.NET class with a Store proc. I specify the name of the
table to my SP then it return's a string. If I Copy/Paste the string in a
VB file, I have a fully fonctional class that represent my table. So, I have
writen a template that I use in my SP. My template has 38000 characters. In
some place, in my template, there are speacials words that are going to be
replace when the SP is execute. When I execute the SP without parameters, my
template is return entirely but with no modification in the template. When I
execute the SP with a parameter, only the 8000 first characters are return
with the modification. I don't run the SP in Query analyser but in a VB.NET
programm that I did. How can I return a big string with parameters?
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> a écrit dans le message de
news: O%23Q81JvhFHA.1044@.tk2msftngp13.phx.gbl...
> How are you determining the length returned? Query Analyzer has a limit
> of 8000 bytes per column. And you should avoid using sp_ as a prefix to
> stored procedures.
> --
> Andrew J. Kelly SQL MVP
>
> "Marc Robitaille" <marc.robitaille@.ars-solutions.caa> wrote in message
> news:%23i2uleuhFHA.1464@.TK2MSFTNGP14.phx.gbl...
>> Hello,
>> How can I return more then 8000 characters from a store proc.?
>> When I do this
>> CREATE PROCEDURE SP_GetClassCode
>> AS
>> SELECT 'very long string'
>> GO
>> It work's
>> But when I do this
>> CREATE PROCEDURE SP_GetClassCode
>> @.Replace AS VARCHAR(50)
>> AS
>> SELECT 'very long ' + @.Replace
>> GO
>> This time it only return the 8000 first characters
>> What do I have to do to return more then 8000 characters from a Store
>> Proc?
>> Thank you
>> Marc R.
>|||It looks like SQL Server is implicitly converting your string to the
datatype of the variable you are concatenating with it. You can create a
table variable with a text column and build your string in the text column.
The issue a select from the table variable at the end to get the full
output.
--
Andrew J. Kelly SQL MVP
"Marc Robitaille" <marc.robitaille@.ars-solutions.caa> wrote in message
news:e4PyfcvhFHA.3124@.TK2MSFTNGP12.phx.gbl...
>I try to build a VB.NET class with a Store proc. I specify the name of the
>table to my SP then it return's a string. If I Copy/Paste the string in a
>VB file, I have a fully fonctional class that represent my table. So, I
>have writen a template that I use in my SP. My template has 38000
>characters. In some place, in my template, there are speacials words that
>are going to be replace when the SP is execute. When I execute the SP
>without parameters, my template is return entirely but with no modification
>in the template. When I execute the SP with a parameter, only the 8000
>first characters are return with the modification. I don't run the SP in
>Query analyser but in a VB.NET programm that I did. How can I return a big
>string with parameters?
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> a écrit dans le message de
> news: O%23Q81JvhFHA.1044@.tk2msftngp13.phx.gbl...
>> How are you determining the length returned? Query Analyzer has a limit
>> of 8000 bytes per column. And you should avoid using sp_ as a prefix to
>> stored procedures.
>> --
>> Andrew J. Kelly SQL MVP
>>
>> "Marc Robitaille" <marc.robitaille@.ars-solutions.caa> wrote in message
>> news:%23i2uleuhFHA.1464@.TK2MSFTNGP14.phx.gbl...
>> Hello,
>> How can I return more then 8000 characters from a store proc.?
>> When I do this
>> CREATE PROCEDURE SP_GetClassCode
>> AS
>> SELECT 'very long string'
>> GO
>> It work's
>> But when I do this
>> CREATE PROCEDURE SP_GetClassCode
>> @.Replace AS VARCHAR(50)
>> AS
>> SELECT 'very long ' + @.Replace
>> GO
>> This time it only return the 8000 first characters
>> What do I have to do to return more then 8000 characters from a Store
>> Proc?
>> Thank you
>> Marc R.
>>
>|||Great idea
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> a écrit dans le message de
news: eFA9CewhFHA.1052@.TK2MSFTNGP10.phx.gbl...
> It looks like SQL Server is implicitly converting your string to the
> datatype of the variable you are concatenating with it. You can create a
> table variable with a text column and build your string in the text
> column. The issue a select from the table variable at the end to get the
> full output.
> --
> Andrew J. Kelly SQL MVP
>
> "Marc Robitaille" <marc.robitaille@.ars-solutions.caa> wrote in message
> news:e4PyfcvhFHA.3124@.TK2MSFTNGP12.phx.gbl...
>>I try to build a VB.NET class with a Store proc. I specify the name of
>>the table to my SP then it return's a string. If I Copy/Paste the string
>>in a VB file, I have a fully fonctional class that represent my table. So,
>>I have writen a template that I use in my SP. My template has 38000
>>characters. In some place, in my template, there are speacials words that
>>are going to be replace when the SP is execute. When I execute the SP
>>without parameters, my template is return entirely but with no
>>modification in the template. When I execute the SP with a parameter, only
>>the 8000 first characters are return with the modification. I don't run
>>the SP in Query analyser but in a VB.NET programm that I did. How can I
>>return a big string with parameters?
>>
>> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> a écrit dans le message
>> de news: O%23Q81JvhFHA.1044@.tk2msftngp13.phx.gbl...
>> How are you determining the length returned? Query Analyzer has a
>> limit of 8000 bytes per column. And you should avoid using sp_ as a
>> prefix to stored procedures.
>> --
>> Andrew J. Kelly SQL MVP
>>
>> "Marc Robitaille" <marc.robitaille@.ars-solutions.caa> wrote in message
>> news:%23i2uleuhFHA.1464@.TK2MSFTNGP14.phx.gbl...
>> Hello,
>> How can I return more then 8000 characters from a store proc.?
>> When I do this
>> CREATE PROCEDURE SP_GetClassCode
>> AS
>> SELECT 'very long string'
>> GO
>> It work's
>> But when I do this
>> CREATE PROCEDURE SP_GetClassCode
>> @.Replace AS VARCHAR(50)
>> AS
>> SELECT 'very long ' + @.Replace
>> GO
>> This time it only return the 8000 first characters
>> What do I have to do to return more then 8000 characters from a Store
>> Proc?
>> Thank you
>> Marc R.
>>
>>
>

return big string

Hello,
How can I return more then 8000 characters from a store proc.?
When I do this
CREATE PROCEDURE SP_GetClassCode
AS
SELECT 'very long string'
GO
It work's
But when I do this
CREATE PROCEDURE SP_GetClassCode
@.Replace AS VARCHAR(50)
AS
SELECT 'very long ' + @.Replace
GO
This time it only return the 8000 first characters
What do I have to do to return more then 8000 characters from a Store Proc?
Thank you
Marc R.Marc Robitaille wrote:
> Hello,
> How can I return more then 8000 characters from a store proc.?
> When I do this
> CREATE PROCEDURE SP_GetClassCode
> AS
> SELECT 'very long string'
> GO
> It work's
> But when I do this
> CREATE PROCEDURE SP_GetClassCode
> @.Replace AS VARCHAR(50)
> AS
> SELECT 'very long ' + @.Replace
> GO
> This time it only return the 8000 first characters
> What do I have to do to return more then 8000 characters from a Store
> Proc?
> Thank you
> Marc R.
You could return the values as two or more columns and concatenate on
the client. The data type you are working with (char/varchar) is limited
to 8000 bytes.
David Gugick
Quest Software
www.imceda.com
www.quest.com|||Is there an other datatype that I could use to return what I need?
"David Gugick" <david.gugick-nospam@.quest.com> a crit dans le message de
news: %235Wco9uhFHA.3256@.TK2MSFTNGP12.phx.gbl...
> Marc Robitaille wrote:
> You could return the values as two or more columns and concatenate on the
> client. The data type you are working with (char/varchar) is limited to
> 8000 bytes.
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com|||How are you determining the length returned? Query Analyzer has a limit of
8000 bytes per column. And you should avoid using sp_ as a prefix to stored
procedures.
Andrew J. Kelly SQL MVP
"Marc Robitaille" <marc.robitaille@.ars-solutions.caa> wrote in message
news:%23i2uleuhFHA.1464@.TK2MSFTNGP14.phx.gbl...
> Hello,
> How can I return more then 8000 characters from a store proc.?
> When I do this
> CREATE PROCEDURE SP_GetClassCode
> AS
> SELECT 'very long string'
> GO
> It work's
> But when I do this
> CREATE PROCEDURE SP_GetClassCode
> @.Replace AS VARCHAR(50)
> AS
> SELECT 'very long ' + @.Replace
> GO
> This time it only return the 8000 first characters
> What do I have to do to return more then 8000 characters from a Store
> Proc?
> Thank you
> Marc R.
>|||I try to build a VB.NET class with a Store proc. I specify the name of the
table to my SP then it return's a string. If I Copy/Paste the string in a
VB file, I have a fully fonctional class that represent my table. So, I have
writen a template that I use in my SP. My template has 38000 characters. In
some place, in my template, there are speacials words that are going to be
replace when the SP is execute. When I execute the SP without parameters, my
template is return entirely but with no modification in the template. When I
execute the SP with a parameter, only the 8000 first characters are return
with the modification. I don't run the SP in Query analyser but in a VB.NET
programm that I did. How can I return a big string with parameters?
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> a crit dans le message de
news: O%23Q81JvhFHA.1044@.tk2msftngp13.phx.gbl...
> How are you determining the length returned? Query Analyzer has a limit
> of 8000 bytes per column. And you should avoid using sp_ as a prefix to
> stored procedures.
> --
> Andrew J. Kelly SQL MVP
>
> "Marc Robitaille" <marc.robitaille@.ars-solutions.caa> wrote in message
> news:%23i2uleuhFHA.1464@.TK2MSFTNGP14.phx.gbl...
>|||It looks like SQL Server is implicitly converting your string to the
datatype of the variable you are concatenating with it. You can create a
table variable with a text column and build your string in the text column.
The issue a select from the table variable at the end to get the full
output.
Andrew J. Kelly SQL MVP
"Marc Robitaille" <marc.robitaille@.ars-solutions.caa> wrote in message
news:e4PyfcvhFHA.3124@.TK2MSFTNGP12.phx.gbl...
>I try to build a VB.NET class with a Store proc. I specify the name of the
>table to my SP then it return's a string. If I Copy/Paste the string in a
>VB file, I have a fully fonctional class that represent my table. So, I
>have writen a template that I use in my SP. My template has 38000
>characters. In some place, in my template, there are speacials words that
>are going to be replace when the SP is execute. When I execute the SP
>without parameters, my template is return entirely but with no modification
>in the template. When I execute the SP with a parameter, only the 8000
>first characters are return with the modification. I don't run the SP in
>Query analyser but in a VB.NET programm that I did. How can I return a big
>string with parameters?
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> a crit dans le message de
> news: O%23Q81JvhFHA.1044@.tk2msftngp13.phx.gbl...
>|||Great idea
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> a crit dans le message de
news: eFA9CewhFHA.1052@.TK2MSFTNGP10.phx.gbl...
> It looks like SQL Server is implicitly converting your string to the
> datatype of the variable you are concatenating with it. You can create a
> table variable with a text column and build your string in the text
> column. The issue a select from the table variable at the end to get the
> full output.
> --
> Andrew J. Kelly SQL MVP
>
> "Marc Robitaille" <marc.robitaille@.ars-solutions.caa> wrote in message
> news:e4PyfcvhFHA.3124@.TK2MSFTNGP12.phx.gbl...
>

return big string

Hello,
How can I return more then 8000 characters from a store proc.?
When I do this
CREATE PROCEDURE SP_GetClassCode
AS
SELECT 'very long string'
GO
It work's
But when I do this
CREATE PROCEDURE SP_GetClassCode
@.Replace AS VARCHAR(50)
AS
SELECT 'very long ' + @.Replace
GO
This time it only return the 8000 first characters
What do I have to do to return more then 8000 characters from a Store Proc?
Thank you
Marc R.
Marc Robitaille wrote:
> Hello,
> How can I return more then 8000 characters from a store proc.?
> When I do this
> CREATE PROCEDURE SP_GetClassCode
> AS
> SELECT 'very long string'
> GO
> It work's
> But when I do this
> CREATE PROCEDURE SP_GetClassCode
> @.Replace AS VARCHAR(50)
> AS
> SELECT 'very long ' + @.Replace
> GO
> This time it only return the 8000 first characters
> What do I have to do to return more then 8000 characters from a Store
> Proc?
> Thank you
> Marc R.
You could return the values as two or more columns and concatenate on
the client. The data type you are working with (char/varchar) is limited
to 8000 bytes.
David Gugick
Quest Software
www.imceda.com
www.quest.com
|||Is there an other datatype that I could use to return what I need?
"David Gugick" <david.gugick-nospam@.quest.com> a crit dans le message de
news: %235Wco9uhFHA.3256@.TK2MSFTNGP12.phx.gbl...
> Marc Robitaille wrote:
> You could return the values as two or more columns and concatenate on the
> client. The data type you are working with (char/varchar) is limited to
> 8000 bytes.
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com
|||How are you determining the length returned? Query Analyzer has a limit of
8000 bytes per column. And you should avoid using sp_ as a prefix to stored
procedures.
Andrew J. Kelly SQL MVP
"Marc Robitaille" <marc.robitaille@.ars-solutions.caa> wrote in message
news:%23i2uleuhFHA.1464@.TK2MSFTNGP14.phx.gbl...
> Hello,
> How can I return more then 8000 characters from a store proc.?
> When I do this
> CREATE PROCEDURE SP_GetClassCode
> AS
> SELECT 'very long string'
> GO
> It work's
> But when I do this
> CREATE PROCEDURE SP_GetClassCode
> @.Replace AS VARCHAR(50)
> AS
> SELECT 'very long ' + @.Replace
> GO
> This time it only return the 8000 first characters
> What do I have to do to return more then 8000 characters from a Store
> Proc?
> Thank you
> Marc R.
>
|||I try to build a VB.NET class with a Store proc. I specify the name of the
table to my SP then it return's a string. If I Copy/Paste the string in a
VB file, I have a fully fonctional class that represent my table. So, I have
writen a template that I use in my SP. My template has 38000 characters. In
some place, in my template, there are speacials words that are going to be
replace when the SP is execute. When I execute the SP without parameters, my
template is return entirely but with no modification in the template. When I
execute the SP with a parameter, only the 8000 first characters are return
with the modification. I don't run the SP in Query analyser but in a VB.NET
programm that I did. How can I return a big string with parameters?
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> a crit dans le message de
news: O%23Q81JvhFHA.1044@.tk2msftngp13.phx.gbl...
> How are you determining the length returned? Query Analyzer has a limit
> of 8000 bytes per column. And you should avoid using sp_ as a prefix to
> stored procedures.
> --
> Andrew J. Kelly SQL MVP
>
> "Marc Robitaille" <marc.robitaille@.ars-solutions.caa> wrote in message
> news:%23i2uleuhFHA.1464@.TK2MSFTNGP14.phx.gbl...
>
|||It looks like SQL Server is implicitly converting your string to the
datatype of the variable you are concatenating with it. You can create a
table variable with a text column and build your string in the text column.
The issue a select from the table variable at the end to get the full
output.
Andrew J. Kelly SQL MVP
"Marc Robitaille" <marc.robitaille@.ars-solutions.caa> wrote in message
news:e4PyfcvhFHA.3124@.TK2MSFTNGP12.phx.gbl...
>I try to build a VB.NET class with a Store proc. I specify the name of the
>table to my SP then it return's a string. If I Copy/Paste the string in a
>VB file, I have a fully fonctional class that represent my table. So, I
>have writen a template that I use in my SP. My template has 38000
>characters. In some place, in my template, there are speacials words that
>are going to be replace when the SP is execute. When I execute the SP
>without parameters, my template is return entirely but with no modification
>in the template. When I execute the SP with a parameter, only the 8000
>first characters are return with the modification. I don't run the SP in
>Query analyser but in a VB.NET programm that I did. How can I return a big
>string with parameters?
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> a crit dans le message de
> news: O%23Q81JvhFHA.1044@.tk2msftngp13.phx.gbl...
>
|||Great idea
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> a crit dans le message de
news: eFA9CewhFHA.1052@.TK2MSFTNGP10.phx.gbl...
> It looks like SQL Server is implicitly converting your string to the
> datatype of the variable you are concatenating with it. You can create a
> table variable with a text column and build your string in the text
> column. The issue a select from the table variable at the end to get the
> full output.
> --
> Andrew J. Kelly SQL MVP
>
> "Marc Robitaille" <marc.robitaille@.ars-solutions.caa> wrote in message
> news:e4PyfcvhFHA.3124@.TK2MSFTNGP12.phx.gbl...
>
sql

Return a value from EXEC

Hi everybody

How to return a value like that
I dont want to use Stored proc.
Thanks

DECLARE @.cpt as integer
DECLARE @.pTable as varchar(40)

Select @.pTable = 'Salaires'
SELECT @.cpt = EXEC('Select Count(*) FROM ' + @.pTable)

Print @.cptdeclare @.i int
declare @.sql nvarchar(1000)
select @.sql = 'Select @.i = Count(*) FROM ' + @.pTable
exec sp_executesql @.sql, N'@.i int out', @.i out
select @.i

see
http://www.nigelrivett.net/sp_executeSQL.html

return @@rowcount from stored proc

Hi

I'm using an sqldatasource control in my aspx page, and then executing it from my code behind page (SqlDataSource1.Insert()), how do i retrieve the number of rows (@.@.rowcount) which have been inserted into the database and display it in my aspx page. I am using a stored procedure.

thanks

Hello Mattock,

Have a look at the following article about using stored procedure to update data:http://msdn2.microsoft.com/en-us/library/59x02y99(VS.80).aspx

Jeroen Molenaar.

sql

Retrieving output parameter from stored proc

I have difficulty reading back the value of an output parameter that I use in a stored procedure. I searched through other posts and found that this is quite a common problem but couldn't find an answer to it. Maybe now there is a knowledgeable person who could help out many people with a good answer.

The problem is that

cmd.Parameters["@.UserExists"].Value evaluates to null. If I call the stored procedure externally from the Server Management Studio Express everything works fine.

Here is my code:

using (SqlConnection cn =new SqlConnection(this.ConnectionString)){ SqlCommand cmd =new SqlCommand("mys_ExistsPersonWithUserName", cn); cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.Add("@.UserName", SqlDbType.VarChar).Value = userName; cmd.Parameters.Add("@.UserExists", SqlDbType.Int); cmd.Parameters["@.UserExists"].Direction = ParameterDirection.Output; cn.Open();int x = (int)cmd.Parameters["@.UserExists"].Value; cn.Close();return (x>1);}

And the corresponding stored procedure:

ALTER PROCEDURE dbo.mys_Spieler_ExistsPersonWithUserName (@.UserNamevarchar(16),@.UserExistsint OUTPUT)ASSET NOCOUNT ONSELECT @.UserExists =count(*)FROM mys_ProfilesWHERE UserName = @.UserNameRETURN

Hey,

Not sure what the problem is, but as an alternative, you can use a return method and define it as return value, or you can just select the result, and in your code, use ExecuteScalar().

sql

Retrieving Output paramater after insert

Can some one offer me some assistance?

I'm using a SQLDataSource control to call a stored proc to insert a record into a control. The stored proc I'm calling has an output paramater that returns the new rows identity field to be used later. I'm having trouble getting to this return value via the SQLDataSource . Below is my code (C#):

SqlDataSource1.InsertParameters["USR_AUTH_NAME"].DefaultValue = storeNumber;

SqlDataSource1.InsertParameters["usr_auth_pwd"].DefaultValue =string.Empty;

SqlDataSource1.InsertParameters["mod_usr_name"].DefaultValue ="SYSTEM";

SqlDataSource1.InsertParameters["usr_auth_id"].Direction =ParameterDirection.ReturnValue;

SqlDataSource1.Insert();

int id =int.Parse(SqlDataSource1.InsertParameters["usr_auth_id"].DefaultValue);

below is the error I'm getting:

System.Data.SqlClient.SqlException: Procedure 'csi_USR_AUTH' expects parameter '@.usr_auth_id', which was not supplied.

Has anyone done this before and if so how did you do it?

How did you define the stored procedure 'csi_USR_AUTH' ? Since you add the Direction of usr_auth_id toParameterDirection.ReturnValue, you don't need to declare a @.usr_auth_id parameter in the sp; instead, it will be filled by using a RETURN command. Check this article to see how to use ReturnValue parameter:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/html/cpconinputoutputparametersreturnvalues.asp

Retrieving @@Identity

Hi,
I have the following proc:
CREATE PROCEDURE SP_CadastraPessoaFisica
(
@.Email Varchar(60) = '',
@.Senha Varchar(10) = '',
@.Nome Varchar(50) = '',
@.Sobrenome Varchar(50) = '',
@.DataNascimento DateTime,
@.Sexo Char(1),
@.CPF Varchar(12)
)
As
Set Nocount On
Insert Into Usuario(Email,Senha) Values(@.Email,@.Senha)
GO
Simple.Isn't it ? So...I need to retrieve the ID from this insert command
when inserted. Should I use @.@.Identity ? How do i do ? I don't know how to
use @.@.Identity!
--
Thanks in advance,
Daniel GrohDaniel
If you are using SQL Server 2000 you can use SCOPE_IDENITY() function
INSERT INTO Table (col) VALUES (1)
SELECT SCOPE_IDENITY()
Note : there is a difference between @.@.IDENTITY and SCOPE_IDENTITY() which
may bring unexpected result
See BOL for details
"Daniel Groh" <newsgroupms@.gmail.com> wrote in message
news:u4mnT$8TFHA.3308@.TK2MSFTNGP14.phx.gbl...
> Hi,
> I have the following proc:
> CREATE PROCEDURE SP_CadastraPessoaFisica
> (
> @.Email Varchar(60) = '',
> @.Senha Varchar(10) = '',
> @.Nome Varchar(50) = '',
> @.Sobrenome Varchar(50) = '',
> @.DataNascimento DateTime,
> @.Sexo Char(1),
> @.CPF Varchar(12)
> )
> As
> Set Nocount On
> Insert Into Usuario(Email,Senha) Values(@.Email,@.Senha)
> GO
> Simple.Isn't it ? So...I need to retrieve the ID from this insert command
> when inserted. Should I use @.@.Identity ? How do i do ? I don't know how to
> use @.@.Identity!
> --
> Thanks in advance,
> Daniel Groh
>|||Please don't multipost ,it is answered in .programming
"Daniel Groh" <newsgroupms@.gmail.com> wrote in message
news:u4mnT$8TFHA.3308@.TK2MSFTNGP14.phx.gbl...
> Hi,
> I have the following proc:
> CREATE PROCEDURE SP_CadastraPessoaFisica
> (
> @.Email Varchar(60) = '',
> @.Senha Varchar(10) = '',
> @.Nome Varchar(50) = '',
> @.Sobrenome Varchar(50) = '',
> @.DataNascimento DateTime,
> @.Sexo Char(1),
> @.CPF Varchar(12)
> )
> As
> Set Nocount On
> Insert Into Usuario(Email,Senha) Values(@.Email,@.Senha)
> GO
> Simple.Isn't it ? So...I need to retrieve the ID from this insert command
> when inserted. Should I use @.@.Identity ? How do i do ? I don't know how to
> use @.@.Identity!
> --
> Thanks in advance,
> Daniel Groh
>|||Hi
You can use this:
SELECT SCOPE_IDENTITY() AS [SCOPE_IDENTITY]
This will help you solve the problem
thanks and regards
Chandra
"Daniel Groh" wrote:
> Hi,
> I have the following proc:
> CREATE PROCEDURE SP_CadastraPessoaFisica
> (
> @.Email Varchar(60) = '',
> @.Senha Varchar(10) = '',
> @.Nome Varchar(50) = '',
> @.Sobrenome Varchar(50) = '',
> @.DataNascimento DateTime,
> @.Sexo Char(1),
> @.CPF Varchar(12)
> )
> As
> Set Nocount On
> Insert Into Usuario(Email,Senha) Values(@.Email,@.Senha)
> GO
> Simple.Isn't it ? So...I need to retrieve the ID from this insert command
> when inserted. Should I use @.@.Identity ? How do i do ? I don't know how to
> use @.@.Identity!
> --
> Thanks in advance,
> Daniel Groh
>
>

Retrieve Guid after inserting recrod with NEWID()

Hi There,

I'm having a problem retreiving the auto generated Guid after inserting anew record with NEWID(), my stored proc is as follows:

SET @.uiTransactionID = NEWID()INSERT INTO Transactions (uiTransactionID) VALUES (@.uiTransactionID)IF @.@.ERROR = 0 AND @.@.ROWCOUNT = 1BEGIN SELECT @.uiTransactionID AS'@.@.GUID' RETURN 0END

And the return on my insert statement is:

command.ExecuteNonQuery();
m_uiTransactionID = (Guid

)command.Parameters["RETURN_VALUE"].Value;

I can never retreive the newly generated Guid, can onyone spot where i'm going wrong?

Many thanks

Ben

Hi,

i have tested your stored procedure and i think the stored procedure is correct. Please check your calling code for the stored procedure:

protected void CallStoredProcedure(){ Guid m_uiTransactionID; SqlCommand cmd =new SqlCommand("Test",new SqlConnection(ConfigurationManager.ConnectionStrings["DataBase"].ToString()));cmd.CommandType = CommandType.StoredProcedure;cmd.Parameters.Add("@.uiTransactionID", SqlDbType.UniqueIdentifier);cmd.Parameters["@.uiTransactionID"].Direction = ParameterDirection.Output;try { cmd.Connection.Open(); cmd.ExecuteNonQuery();m_uiTransactionID = (Guid)cmd.Parameters["@.uiTransactionID"].Value; }catch (Exception exc) { }finally { cmd.Connection.Close(); }}

And you can also check the stored procedure execute permissons.

Hope this can help you.

Regards
Marc André

|||

Hi Marc,

Thanks for your reply, you've helped me agreat deal and it is now working.

Many thnks

Ben