How to get type of parameter passed in stored procedure?

Hi ,

I wrote a stored procedure with one uinqueidentifier parameter.Depending upon the value passed during execution type of parameter passed is changing.

Example:

CREATE PROCEDURE ABC_Proc

@.ABC uniqueidentifier = NULL

Execution:

EXEc ABC_Proc ' '

If I pass the uiqueidentifier from the same table then the program executes properly.If same uinqueidentifier from other table or NULL or '' is passed the following error is displayed

"conversion failed when converting from a character string to unique identifier."

Questions:IS there a way to get the datatype of parameter passed in the querry?so, that I can modify accordingly.

Thanks,

Kodela.

Hi Kodela,

The easiest way is to use the new sql_variant datatype as the parameter, and then within the procedure, you can test for the base datatype and execute the appropriate action:

declare @.v sql_variant

set @.v = 123

if SQL_VARIANT_PROPERTY ( @.v , 'BaseType' ) = 'int'
begin
print 'int type passed'
end
else if SQL_VARIANT_PROPERTY ( @.v ,'BaseType' ) = 'uniqueidentifier'
begin
print 'uid type passed'
end

Cheers,
Rob

|||

You could use sql_variant, but the best thing to do is to treat parameters as strongly typed and only pass proper values to that parameter. You can get the type of the parameter from the information_schema:

CREATE PROCEDURE ABC_Proc
@.ABC uniqueidentifier = NULL
as
select @.ABC
go

select parameter_name, data_type
from information_schema.parameters
where specific_name = 'ABC_Proc'
and specific_schema = 'dbo'

|||

Robert Varga wrote:

use the new sql_variant datatype

Robert,

There is a reason that the variant datatype has been removed from Visual Studio.Net -it opens the code to potential attack vectors. Similarly, it is not a particularly good thing to use in SQL Server. Often the use of variants demonstrates a poor design, or just plain sloppy coding. -NOT drawing aspersions to you or your techniques, just commenting...

Regards,

|||

Hi Arnie,

I have to say I disagree completely. The SQL_VARIANT datatype was introduced in SQL 2005 for good reason. Just as there are valid reasons for using cursors, there are valid reasons for using the sql_variant datatype, and blanket statements alluding to their use constituting "poor design" are flawed.

In reality, we've been using varchar's as "variant" datatypes for years - ( CONVERT(varchar(15), GETDATE(), 103) anyone? ) and the new datatype removes precision related errors.

Cheeers,
Rob

|||

I actually have to agree that the SQL_VARIANT datatype should generally be avoided (as should converting dates to varchar values too, since that is usually a formatting gimmick that we *should* let the presentation layer handle :) for the same reason that VB programmers shouldn't generally use Option Strict (I think that was (is?) the syntax).

Especially in this situation, picking the proper datatype is part of the contract between caller and callee. In your example, it would not be possible to tell a GUID from a character value, certainly not when being passed in as a parameter, since it would be passed as a string of characters (or as a binary value) which could not be distinguished in code.

It is not completely useless when used for sparse property tables where you define the metadata of the column at runtime is a pretty good use, but generally having a column of unknown type means more code, more work, and greater chance for errors later.

|||

Hi Louis,

Sure; just as liberal use of, say, cursors is not a good thing, neither is the use of generic datatypes. My point is that there are circumstances in which the use of sql_variant (or cursors) are valid and should not be discounted by zealots simply because some Wrox book said they were "bad".

Cheers,
Rob

|||

Louis,

You demonstrate my point precisely. There is no need to use a sql_variant in this situation. The metadata is available. Strongly typed variables/parameters reduce coding and coding errors.

Using an object datatype (the .NET replacement for 'variant' has it's place and needs, and slq_variant has it's places and usages. However, in my opinion, it should not be used in a situation where the exact need does not exist. This is not one of those situations.

And gosh, if one wanted to morph a varchar() parameter, the datatype could be just another input parameter. For me, strongly typing is the key. If the dataype is known when the call is made, it should be strongly typed.

How to get todays date in format YY/MM/DD and to compare it to another date passed into th

I need to do the following and am hoping someone can help me out.

I have C#(asp.net app) that will call a stored procedure. The C# will pass in a date to the
stored procedure. The date is in the format YY/MM/DD. Once inside of the stored procedure, the date
passed into the stored proc needs to be compared to todays date. Todays date must be determined in
the SQL.

So basically here is my pseudo code for what I am trying to accomplish. Basically I just am after
the comparison of the two values:

If @.BeginDate < TodaysDate

The difficult part is how to obtain the value for "TodaysDate"

Taking into consideration that "TodaysDate" should probably be in the format of YY/MM/DD considering that is how the date it is to be compared with is being passed in.

Can someone please code this out for me in Microsoft SQL. I would be forever grateful.

I figured out what I needed to know, but will have further questions and will need help. Thanks to all.

How to get the system date and time into database

Hi,

I m using ASP.NET with C#.
I m having one field which should store the datetime of the system.

The datetime should be automatically stored for that entry when the user submits that record on the click event.
Then it should display the date time on the gridview from database.

I m using sqlserver 2005 and i have created the stored procedure for the insert command.

Thisis the sample sp what should be written here to insert system date timeautomatically when the user submits the asp.net form ?

Is there any code for writing directly in stored procedure or asp.net coding page...

ALTER PROCEDURE [dbo].[StoredProcedure1]
@.salesid INT OUTPUT,
@.salesdate datetime,
@.customername varchar(20)
AS
BEGIN
SET NOCOUNT ON
BEGIN
 INSERT INTO sales (customername)
 VALUES (@.customername)
 SELECT @.companyid = SCOPE_IDENTITY()
END
SET NOCOUNT OFF
END

Thanxs in advance...

Hi,

use getdate() function of sql server.

for more info look here

http://msdn2.microsoft.com/en-us/library/ms188383.aspx

|||

You have a choice as to where to use the getdate() function (which returns the current system date and time).

You can actually define it on a column in the table itself so that you don't have to set it programmatically. Here is sample syntax:

create table sales (sales_id int identity, customer_name varchar(30) not null,sales_date datetime default getdate() not null)

Alternatively, within the stored procedure assign your variable with the function call. Here is sample syntax:

select @.SystemDate = getdate()

How to get the stored procedure return type

I am having a problem finding the stored procedure return type. I am having no problem with the getting the stored procedure parameters collection and processing as needed or determining if the parameter is an output column. But if the stored procedure returns a value, how to I determine that return value type with SMO. I would much appreciate it if someone could answer this...

Steve Graddy
Orgbrat Consulting

Stored Procedures return multiple values in the form of output parameters. Thus the return types would be the datatype of StoredProcedure.Parameters which have IsOutputParameter as true.
Hope that helps.

Thanks,
Kuntal

|||

foreach (StoredProcedureParameter col inthis.Database.StoredProcedures[spName].Parameters)

{

Console.WriteLine("{0} , {1}, {2}, {3}", col.ID, col.Name, col.IsOutputParameter,col.DataType);

}

|||

Ok I am confused.. I understand about the ISOutputParameter property. let me give you an example and maybe you can explain. Using the Pub's database and the "reptq3" stored procedure as an example. if you look at this stored procedure definition there is no output parameter, but when you look at the parameters list in the object tree of the SQL Server 2005 Management Studio the last parameter or item is "Returns Integer". But when you get the stored procedures parametr collaection with SMO, you only get the first three parametrs that are in the stored procedure parameter list. Where is the SQL Server 2005 Management Studio coming up with that last item or does it always assume that if there are no output parametrs declared, there is always a last item "Returns Integer"? Here is a quick and dirty code section and as you see, I am asking for every paramter and then handling if it is input or output in the application. Probklem is, I only get three parametrs back from this call.

StoredProceduresp = db.StoredProcedures[ procName ];
DataTable dtReport = Util.GetStructStoredProcBasic();
foreach ( Microsoft.SqlServer.Management.Smo.StoredProcedureParameter spp in sp.Parameters ) {
DataRow drReport = dtReport.NewRow();
drReport[ "ProcName" ] = sp.Name;
drReport[ "Column_Name" ] = spp.Name;

if ( spp.IsOutputParameter )
drReport[ "Column_Type" ] = 2;
else
drReport[ "Column_Type" ] = 1;
drReport[ "Type_Name" ] = spp.DataType.Name;

dtReport.Rows.Add( drReport );
}

Steve Graddy
Orgbrat Consulting

|||

A stored procedure always returns an integer whose value is 0 if the execution was successful and non-zero in case of any failure. The return parameter which is mentioned above is different from this. From BOL - A stored procedure Return a status value to a calling procedure or batch to indicate success or failure (and the reason for failure).

How to get the stored procedure return type

I am having a problem finding the stored procedure return type. I am having no problem with the getting the stored procedure parameters collection and processing as needed or determining if the parameter is an output column. But if the stored procedure returns a value, how to I determine that return value type with SMO. I would much appreciate it if someone could answer this...

Steve Graddy
Orgbrat Consulting

Stored Procedures return multiple values in the form of output parameters. Thus the return types would be the datatype of StoredProcedure.Parameters which have IsOutputParameter as true.
Hope that helps.

Thanks,
Kuntal

|||

foreach (StoredProcedureParameter col inthis.Database.StoredProcedures[spName].Parameters)

{

Console.WriteLine("{0} , {1}, {2}, {3}", col.ID, col.Name, col.IsOutputParameter,col.DataType);

}

|||

Ok I am confused.. I understand about the ISOutputParameter property. let me give you an example and maybe you can explain. Using the Pub's database and the "reptq3" stored procedure as an example. if you look at this stored procedure definition there is no output parameter, but when you look at the parameters list in the object tree of the SQL Server 2005 Management Studio the last parameter or item is "Returns Integer". But when you get the stored procedures parametr collaection with SMO, you only get the first three parametrs that are in the stored procedure parameter list. Where is the SQL Server 2005 Management Studio coming up with that last item or does it always assume that if there are no output parametrs declared, there is always a last item "Returns Integer"? Here is a quick and dirty code section and as you see, I am asking for every paramter and then handling if it is input or output in the application. Probklem is, I only get three parametrs back from this call.

StoredProceduresp = db.StoredProcedures[ procName ];
DataTable dtReport = Util.GetStructStoredProcBasic();
foreach ( Microsoft.SqlServer.Management.Smo.StoredProcedureParameter spp in sp.Parameters ) {
DataRow drReport = dtReport.NewRow();
drReport[ "ProcName" ] = sp.Name;
drReport[ "Column_Name" ] = spp.Name;

if ( spp.IsOutputParameter )
drReport[ "Column_Type" ] = 2;
else
drReport[ "Column_Type" ] = 1;
drReport[ "Type_Name" ] = spp.DataType.Name;

dtReport.Rows.Add( drReport );
}

Steve Graddy
Orgbrat Consulting

|||

A stored procedure always returns an integer whose value is 0 if the execution was successful and non-zero in case of any failure. The return parameter which is mentioned above is different from this. From BOL - A stored procedure Return a status value to a calling procedure or batch to indicate success or failure (and the reason for failure).

How to get the stored procedure return type

I am having a problem finding the stored procedure return type. I am having no problem with the getting the stored procedure parameters collection and processing as needed or determining if the parameter is an output column. But if the stored procedure returns a value, how to I determine that return value type with SMO. I would much appreciate it if someone could answer this...

Steve Graddy
Orgbrat Consulting

Stored Procedures return multiple values in the form of output parameters. Thus the return types would be the datatype of StoredProcedure.Parameters which have IsOutputParameter as true.
Hope that helps.

Thanks,
Kuntal

|||

foreach (StoredProcedureParameter col inthis.Database.StoredProcedures[spName].Parameters)

{

Console.WriteLine("{0} , {1}, {2}, {3}", col.ID, col.Name, col.IsOutputParameter,col.DataType);

}

|||

Ok I am confused.. I understand about the ISOutputParameter property. let me give you an example and maybe you can explain. Using the Pub's database and the "reptq3" stored procedure as an example. if you look at this stored procedure definition there is no output parameter, but when you look at the parameters list in the object tree of the SQL Server 2005 Management Studio the last parameter or item is "Returns Integer". But when you get the stored procedures parametr collaection with SMO, you only get the first three parametrs that are in the stored procedure parameter list. Where is the SQL Server 2005 Management Studio coming up with that last item or does it always assume that if there are no output parametrs declared, there is always a last item "Returns Integer"? Here is a quick and dirty code section and as you see, I am asking for every paramter and then handling if it is input or output in the application. Probklem is, I only get three parametrs back from this call.

StoredProceduresp = db.StoredProcedures[ procName ];
DataTable dtReport = Util.GetStructStoredProcBasic();
foreach ( Microsoft.SqlServer.Management.Smo.StoredProcedureParameter spp in sp.Parameters ) {
DataRow drReport = dtReport.NewRow();
drReport[ "ProcName" ] = sp.Name;
drReport[ "Column_Name" ] = spp.Name;

if ( spp.IsOutputParameter )
drReport[ "Column_Type" ] = 2;
else
drReport[ "Column_Type" ] = 1;
drReport[ "Type_Name" ] = spp.DataType.Name;

dtReport.Rows.Add( drReport );
}

Steve Graddy
Orgbrat Consulting

|||

A stored procedure always returns an integer whose value is 0 if the execution was successful and non-zero in case of any failure. The return parameter which is mentioned above is different from this. From BOL - A stored procedure Return a status value to a calling procedure or batch to indicate success or failure (and the reason for failure).

how to get the status of update sucessing?

Dear Experts,

I have a stored procudure which can be successful called from application and update against tables, the question is how do I get the status which can indicate how many records were updated or no any record was updated in the action?

Thanks in advance.

Hi,

@.@.ERROR returns the error number for the last T-SQL statement executed while
@.@.ROWCOUNT returns the number of rows affected by the last SQL statement.

How to get the returning value of a stored procedure?

Hi everyone!
I am new to sql server 2005 and visual studio 2005.

I have the following simple stored procedure that checks if a user exists:
-------------------------------
ALTER PROCEDURE [dbo].[sp_Users_AlreadyExists]

@.UserName varchar(256)
AS
BEGIN
SET NOCOUNT ON;

IF (EXISTS (SELECT UserName FROM dbo.Users WHERE LOWER(@.UserName) = LoweredUserName ))
RETURN(1)
ELSE
RETURN(0)
END
-------------------------------

I use the following code to execute the procedure on visual studio:
-------------------------------
.
.
.
cmdobj As SqlCommand
cmdobj = New SqlCommand(sp_Users_AlreadyExists, connobj)
cmdobj.CommandType = CommandType.StoredProcedure
cmdobj.Parameters.AddWithValue("@.UserName", "blablalala")
cmdobj.ExecuteNonQuery()
cmdobj.Dispose()
connobj.Close()
.
.
.
-------------------------------

I expected that cmdobj.ExecuteNonQuery() would return 1 if the userblablab exists or 0 if the user doesnt, but it just return -1 (i thinkbecause no row was affected)

Does anyone knows how to retrieve the value that my stored procedure returns?

Thanx in advance!

cmdobj.parameters.add("@.RETURN_VALUE",sqldbtype.int).direction=returnvalue

Then check the parameter value after the execute like:

dim val as integer=cmdobj.parameters("@.RETURN_VALUE").value

sql

How to get the returned value from a stored procedure?

Hi,

I use a strong-typed DataSet to build the data access tier of my application, and the IDE generates most of code for me. However I can't find a way to get the value returned by a stored procedure(The stored procedure seems like 'RETURN 0').I noticed that a @.RETURN_VALUE parameter is added automatically for each query method, but I don't know how to read the value.

Anybody could help me with the problem? Thanks.

Hi,

I am assuming you are trying to return the value via VB or C#. You must load your sql command in to a data reader. It loads your values in an an array which is index. Here is an example which returns numerous values and assigns one of the values to a label.

VB:
Dim myConnectionAs SqlConnection
Dim myCommandAs SqlCommand
 myConnection =New SqlConnection("Data Source=MYCOMPUTER\SQLEXPRESS;Initial Catalog=MYDATABASEIntegrated Security=True")
 myConnection.Open()
' sql statment
 myCommand =New SqlCommand("Exec MY_SP", myConnection)
Dim dr = myCommand.ExecuteReader()
Dim iAs Integer = 1
While dr.read()

 Me.lblmel.Text = dr(2).ToString
 End While
 dr.Close()
 myConnection.Close()

C#:

 SqlConnection myConnection;
 SqlCommand myCommand;
 myConnection =new SqlConnection("Data Source=MYCOMPUTER\\SQLEXPRESS;Initial Catalog=MYDATABASEIntegrated Security=True");
 myConnection.Open();
// sql statment
 myCommand =new SqlCommand("Exec MY_SP", myConnection);
object dr = myCommand.ExecuteReader();
while (dr.read()) {
this.lblmel.Text = dr(2).ToString;
 }
dr.Close();
myConnection.Close();

|||

U can use outparameter to get the value

Thank u

Baba

Please remember to click "Mark as Answer" on this post if it helped you.

How to get the return value when using a TableAdapter access a Stored Procedure

I have a Stored Procedure

CREATE PROCEDURE test
AS
BEGIN
SELECT Count(*) FROM dbo.test
END

I can using the unbox get the return value

but if i direct return a value form a Stored Procedure like this

CREATE PROCEDURE test
AS
BEGIN
return 100
END

I can not get the VALUE
I do not know how to
Please Help Me
thx

You can use such T-SQL statements to get the result from the stored procedure in SQL Server:

declare @.s int
exec @.s=test
select @.s

How to get the return value when using a TableAdapter access a Stored Procedure

I have a Stored Procedure

CREATE PROCEDURE test
AS
BEGIN
SELECT Count(*) FROM dbo.test
END

I can using the unbox get the return value

but if i direct return a value form a Stored Procedure like this

CREATE PROCEDURE test
AS
BEGIN
return 100
END

I can not get the VALUE
I do not know how to
Please Help Me
thx

Hi,

and welcome to the ASP.NET forums.

You can use aSqlParameter with Direction set toParameterDirection.ReturnValue. Pleasetake a look at this little code sample.

Grz, Kris.

How to get the return value of a stored procedure

I have a Stored procedure (sql 2000), that inserts data into a table. Then, I add this, at the end:
Return Scope_Identity()

I have the parameters for the sProc defined and added to the Command, but I'm having a really lousy time trying to figure out how to get the return value of the Stored PRocedure. BTW - I'm using OleDB instead of SQL due to using a UDL for the connection string.

I have intReturn defined as an integer

I've tried :
Dim retValParam As OleDbParameter = cmd.Parameters.Add("@.RETURN_VALUE", OleDbType.Integer)
retValParam.Direction = ParameterDirection.ReturnValue
intReturn=cmd.Parameters("@.RETURN_VALUE").Value

whenever I add this section - I get an error that there are too many arguments for the sProc.

I've tried:
intreturn=cmd.ExecuteNonquery - tried adding a DataReader - using ExecuteScalar - I've tried so many things and gotten so many errors - I've forgotten which formations go with which errors.

What is the best way to do this in the code part (VB.Net)?

Thanks ahead of time

check the second part ofthis article

|||

Turned out, I didn't even use a parameter for the Returnvalue -

I ended up using something I'd tried before :
lg=cmd.ExecuteScalar....and it worked.

sql

How to get the results when executing extended stored procedures.

Hi. Does anyone know how to display the results if i execute "xp_fixeddrives, xp_availablemedia and xp_subdirs" commands with VC++ 6.0? I can't obtained the results using Recordset class. Can someone help me? Thank you.

|_N_T_|

See if the following works for you:

create table #FreeSpace(

Drive char(1),

MB_Free int)

insert into #FreeSpace exec xp_fixeddrives

select * from #freespace

|||

For some reason, that didn't work for me but this did:

Declare @.FreeSpace TABLE (

Drive char(1),

MB_Free int)

insert into @.FreeSpace exec xp_fixeddrives

select * from @.freespace

Go figure...

How to get the results when executing extended stored procedures.

Hi. Does anyone know how to display the results if i execute "xp_fixeddrives, xp_availablemedia and xp_subdirs" commands with VC++ 6.0? I can't obtained the results using Recordset class. Can someone help me? Thank you.

|_N_T_|

See if the following works for you:

create table #FreeSpace(

Drive char(1),

MB_Free int)

insert into #FreeSpace exec xp_fixeddrives

select * from #freespace

|||

For some reason, that didn't work for me but this did:

Declare @.FreeSpace TABLE (

Drive char(1),

MB_Free int)

insert into @.FreeSpace exec xp_fixeddrives

select * from @.freespace

Go figure...

How to get the output parameter from an internally called stored procedure??

Hi all,
i have a problem and couldnt find anything even close to it. please help me, here is the description of what i m trying to accomplish:
I have a trigger that is generating a column value and calling a stored procedure after the value is generated. And this stored procedure is setting this generated value as an output parameter. But my problem is:
my asp.net page is only sending an insert parameter to the table with the trigger, trigger is running some code depending on the insert parameter and calling this other stored procedure internally. So basically i m not calling this last stored procedure that sets the output parameter within my web form. How can i get the output parameter in my webform? Everthing is working now, whenever an insert hits the table trigger runs and generates this value and called stored procedure sets it as an output parameter. I can get the output parameter with no problem in query analyzer, so the logic has no problem but i have no idea how this generated output parameter can be passed in my webform since its not initiated there.
any help will greately be appreciated, i m sure asp.net and sql server 2000 is powerful and flexible enough to accomplish this but how??
-shane

Try these links the last link is a tool that will generate the code for you but read the articles first so you know what you are doing. Hope this helps.
http://msdn.microsoft.com/msdnmag/issues/05/05/DataPoints/default.aspx

http://www.codeguru.com/vb/gen/vb_database/sqlserver/article.php/c8671__3/

http://www.adonetexpress.com/default.aspx

|||thanks for the links, there are great information there but my problemis not with calling stored procedures within a page or creating astored procedure. my problem is getting the output parameter of astored procedure that is called within a trigger. From my understandingthis is not possible but i wanted to ask and make sure anyway.
thank you,
-shane

|||Can you post a sample code to demonstrate the issue? No need to post it all, if it is huge but something that would ease up reproing it.|||Hi,
i dont have the full code here but its something similat to below (ignore syntax)
my trigger:
instead of insert
as
declare @.ID int
select @.ID = select ID from inserted
begin
update mytable(mycolumn) --if the inserted parameter(@.ID) exists mycolumn will be updated
select mycolumn=mycolumn + 1
where mytable.myid in (select ID from inserted)
insert into mytable(mycolumn) -- if the inserted parameter doesnt exists it will be inserted with a mycolumn=0
values(0)
where mytable.myid not in (select ID from inserted)
end
myprocedure(@.ID)
go
the above trigger inserts a new row with mycolumn=0 to mytable if theinput parameter doesnt exists in mytable, if it exists instead of aninsert an update fires and the value of mycolumn increments one. Afterinsert/update finished i m calling myprocedure which will set the newvalue of mycolumn as an output parameter.
myprocedure
(@.ID int,
@.mycolumn int output)
select @.mycolumn=mycolumn from mytable
where ID = @.ID
please ignore the syntax mistakes, the actual code is working with no problem.
What i m trying to do is run an insert procedure or sql query with aparameter(@.ID) and get the output parameter which is @.mycolumn. Butthis mycolumn will be set to its final value after the trigger runs.
Is it possible?
thanks,
-shane
|||I am assuming this is what you need, try this link for the code to an undocumented stored proc calledsp_executesql. Hope this helps.
http://support.microsoft.com/default.aspx?scid=kb;en-us;262499|||It looks like something i might need, i will test it asap.
Any idea how to get the output parameter created/kept by sp_execuresql into my asp.net page?
my understanding is sp_executesql will call my stored procedure andkeep its output parameter as an output parameter but i still dont knowhow to use this parameter in my page.
thank you,
-shane
|||Run a search for "Using sp_executesql" in the BOL (books online) it shows some examples of what you can do but there is no ADO.NET code. Hope this helps.|||thank you,
thats what i ll do. thanks again for the link
-shane
sql

How to get the Out put from the Stored Procedure

Hi Every body,

i am trying to execute a stored procedure and want the out put to be passed to another database table.

I tried to create a OLEDB Source and gave the Exec Procedure Statement. I tried to see the preview and able to see the out put results. But when i am clicking on the columns to map to the destination i am not able to see the metadata.

Can you guys pls let me know how to do this.

thanx in advance..

Regards,

Dev

If your stored procedure is complex, you may need to insert a SELECT that returns the expected columns as the first statement in your stored proc. You can add a WHERE clause like WHERE 0 = 1 to ensure that no rows are returned.

When you are using a multiple operation stored procedures, a lot of tools (SSIS included) use the first resultset to determine the metadata for the stored proc. You can add a "dummy" resultset to ensure that it gets the right metadata.

|||

Hi,

I really appreciate your early reply. i will give a try and let you know the out come.

Thanx & Regards,

Dev

|||

hi,

can you pls let me know how to write a dummy SQL Statement for the out put, as i am not able to get the table dosent existsin the Database

Dev

|||

Hi,

I tried a sample like this

1. Created a SP

ALTERprocedure [dbo].[usp_test]

as

begin

declare @.error_number int,

@.row_count int

CREATETABLE #temp (

test1 varchar(50),

test2 varchar(50)

)

SELECT*from #temp where 0=1;

end

2. Added OLEDB SOURCE -- > added as a SQL Command EXEC usp_test.

3. now when i click on the metadata it's not showing up the coloumns

Please suggest what i am doing wrong

Regds,

Dev

|||

SELECT '' AS mystringcolumn, 1 as myintcolumn WHERE 1 = 0

The columns here need to match your expected resultset, both in name and type.

|||You need to put the select before the create table. See my post above.|||

Add this t-sql at the end of the storedproc

declare @.sql varchar (50 )

selelct @.sql='select * from #temp where 0=1'

Exec (@.sql)

give it a try

How to get the null values in my stored procedure i am getting error dbnullvalue on front

I have the following stored proc. which i am using on the front end to get all the record from table:

if there are any fields it has anynull values in it i am getting error dbnull value error.
i have null value for ReviewerComment field, can you please tell me how to pass a "" if it is null, in the store proc only, to get all the fresh dat to front end before bnding it to the datagrid control.

CREATE PROCEDURE [dbo].[sp_displayrevws]
AS
select r.RevID,
rtrim(f.revwfunction) as revwfunction,
rtrim(u.uname) as uname,
CONVERT(varchar(10),r.Issued,101) as Issued,
r.ReviewerComment,
r.Response,
r.ModuleID,
rtrim(r.ModuleName) as modulename,
r.ReviewerUserID,r.RevFunctionid,r.Dispositionid
from TAB_ccsNetReviewers r, tabuname u, ccsfunctions f, ccsdisposition d where u.id = r.ReviewerUserID and r.RevFunctionid = f.id and r.Dispositionid = d.id and r.ModuleID = 1 order by r.RevID ASC
GO

Thank you very much.Note use of IsNull() function.

CREATE PROCEDURE [dbo].[sp_displayrevws]
AS
select r.RevID,
rtrim(f.revwfunction) as revwfunction,
rtrim(u.uname) as uname,
CONVERT(varchar(10),r.Issued,101) as Issued,
IsNull(r.ReviewerComment,''),
r.Response,
r.ModuleID,
rtrim(r.ModuleName) as modulename,
r.ReviewerUserID,r.RevFunctionid,r.Dispositionid
from TAB_ccsNetReviewers r, tabuname u, ccsfunctions f, ccsdisposition d where u.id = r.ReviewerUserID and r.RevFunctionid = f.id and r.Dispositionid = d.id and r.ModuleID = 1 order by r.RevID ASC
GO

|||You can use the case statement in sql to look at a field before its returned. For example, you could do this (uses the pubs db)

select au_lname, au_fname,
AuthorCity = Case when (city is null) then '' else city end
from authors

if any city values are null, they'll come back as a '' empty string.

Hope this helps,

Scott

How to get the last command of a SPID if not using the DBCC inputbuffer?

hi,
I want to get all the server's processes last command in a stored procedure.
What I know now is that I could get the last command of one specific process
by using DBCC Inputbuffer, but I could not insert the result into a table.
Any one has other ways to get the last command and could save in a table?
I am using SQL Server 2000.
Thanks in advance
FrankFrank
> but I could not insert the result into a table
create table #test
(
col1 varchar(500),
col2 int,
col3 varchar(1000)
)
insert into #test exec ('dbcc inputbuffer(99)')
select * from #test
"Frank" <wangping@.lucent.com> wrote in message
news:umRj%23fmwFHA.3756@.TK2MSFTNGP10.phx.gbl...
> hi,
> I want to get all the server's processes last command in a stored
> procedure.
> What I know now is that I could get the last command of one specific
> process
> by using DBCC Inputbuffer, but I could not insert the result into a table.
> Any one has other ways to get the last command and could save in a table?
> I am using SQL Server 2000.
> Thanks in advance
> Frank
>|||Hi Frank
Try (for SPID 52):
CREATE TABLE #inputbuffer (
EventType varchar(20),
parameter INT,
Eventinfo varchar(80)
)
-- Execute the command, putting the results in the table
INSERT INTO #inputbuffer
EXEC ('DBCC INPUTBUFFER (52) WITH NO_INFOMSGS')
-- Display the results
SELECT *
FROM #inputbuffer
GO
John
"Frank" wrote:
> hi,
> I want to get all the server's processes last command in a stored procedure.
> What I know now is that I could get the last command of one specific process
> by using DBCC Inputbuffer, but I could not insert the result into a table.
> Any one has other ways to get the last command and could save in a table?
> I am using SQL Server 2000.
> Thanks in advance
> Frank
>
>|||Thanks Dimant!
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%23xVBJpmwFHA.460@.TK2MSFTNGP15.phx.gbl...
> Frank
> > but I could not insert the result into a table
> create table #test
> (
> col1 varchar(500),
> col2 int,
> col3 varchar(1000)
> )
> insert into #test exec ('dbcc inputbuffer(99)')
> select * from #test
>
> "Frank" <wangping@.lucent.com> wrote in message
> news:umRj%23fmwFHA.3756@.TK2MSFTNGP10.phx.gbl...
> > hi,
> > I want to get all the server's processes last command in a stored
> > procedure.
> > What I know now is that I could get the last command of one specific
> > process
> > by using DBCC Inputbuffer, but I could not insert the result into a
table.
> > Any one has other ways to get the last command and could save in a
table?
> > I am using SQL Server 2000.
> >
> > Thanks in advance
> > Frank
> >
> >
>|||John,
Thanks for your information
Frank
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:86A3B320-1959-48B7-90A0-6BCCC77793BA@.microsoft.com...
> Hi Frank
> Try (for SPID 52):
> CREATE TABLE #inputbuffer (
> EventType varchar(20),
> parameter INT,
> Eventinfo varchar(80)
> )
> -- Execute the command, putting the results in the table
> INSERT INTO #inputbuffer
> EXEC ('DBCC INPUTBUFFER (52) WITH NO_INFOMSGS')
> -- Display the results
> SELECT *
> FROM #inputbuffer
> GO
> John
> "Frank" wrote:
> > hi,
> > I want to get all the server's processes last command in a stored
procedure.
> > What I know now is that I could get the last command of one specific
process
> > by using DBCC Inputbuffer, but I could not insert the result into a
table.
> > Any one has other ways to get the last command and could save in a
table?
> > I am using SQL Server 2000.
> >
> > Thanks in advance
> > Frank
> >
> >
> >

How to get the last command of a SPID if not using the DBCC inputbuffer?

hi,
I want to get all the server's processes last command in a stored procedure.
What I know now is that I could get the last command of one specific process
by using DBCC Inputbuffer, but I could not insert the result into a table.
Any one has other ways to get the last command and could save in a table?
I am using SQL Server 2000.
Thanks in advance
FrankFrank
> but I could not insert the result into a table
create table #test
(
col1 varchar(500),
col2 int,
col3 varchar(1000)
)
insert into #test exec ('dbcc inputbuffer(99)')
select * from #test
"Frank" <wangping@.lucent.com> wrote in message
news:umRj%23fmwFHA.3756@.TK2MSFTNGP10.phx.gbl...
> hi,
> I want to get all the server's processes last command in a stored
> procedure.
> What I know now is that I could get the last command of one specific
> process
> by using DBCC Inputbuffer, but I could not insert the result into a table.
> Any one has other ways to get the last command and could save in a table?
> I am using SQL Server 2000.
> Thanks in advance
> Frank
>|||Thanks Dimant!
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%23xVBJpmwFHA.460@.TK2MSFTNGP15.phx.gbl...
> Frank
> create table #test
> (
> col1 varchar(500),
> col2 int,
> col3 varchar(1000)
> )
> insert into #test exec ('dbcc inputbuffer(99)')
> select * from #test
>
> "Frank" <wangping@.lucent.com> wrote in message
> news:umRj%23fmwFHA.3756@.TK2MSFTNGP10.phx.gbl...
table.[vbcol=seagreen]
table?[vbcol=seagreen]
>

How to get the last command of a SPID if not using the DBCC inputbuffer?

hi,
I want to get all the server's processes last command in a stored procedure.
What I know now is that I could get the last command of one specific process
by using DBCC Inputbuffer, but I could not insert the result into a table.
Any one has other ways to get the last command and could save in a table?
I am using SQL Server 2000.
Thanks in advance
Frank
Frank
> but I could not insert the result into a table
create table #test
(
col1 varchar(500),
col2 int,
col3 varchar(1000)
)
insert into #test exec ('dbcc inputbuffer(99)')
select * from #test
"Frank" <wangping@.lucent.com> wrote in message
news:umRj%23fmwFHA.3756@.TK2MSFTNGP10.phx.gbl...
> hi,
> I want to get all the server's processes last command in a stored
> procedure.
> What I know now is that I could get the last command of one specific
> process
> by using DBCC Inputbuffer, but I could not insert the result into a table.
> Any one has other ways to get the last command and could save in a table?
> I am using SQL Server 2000.
> Thanks in advance
> Frank
>
|||Thanks Dimant!
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%23xVBJpmwFHA.460@.TK2MSFTNGP15.phx.gbl...[vbcol=seagreen]
> Frank
> create table #test
> (
> col1 varchar(500),
> col2 int,
> col3 varchar(1000)
> )
> insert into #test exec ('dbcc inputbuffer(99)')
> select * from #test
>
> "Frank" <wangping@.lucent.com> wrote in message
> news:umRj%23fmwFHA.3756@.TK2MSFTNGP10.phx.gbl...
table.[vbcol=seagreen]
table?
>