Executing procedure for empty result set return

I am running SQL 2005, SP1.
Is there a way to execute a stored procedure (which returns a result set) an
d
instead of returning the result set, just returning an empty result set, or
in other words, what would be the column names of the result set?
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200708/1you will have to modifiy the procedure.
add a paramter to it like @.IncludeResults and pass a 1 when you want
it to return results or a zero when you don't.
in the where clause of the final select statement in the procedure add
"And 1 = @.IncludeResults"|||You can try SET FMTONLY ON.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"cbrichards via droptable.com" <u3288@.uwe> wrote in message news:767421b3eff7f@.uwe...[vbcol
=seagreen]
>I am running SQL 2005, SP1.
> Is there a way to execute a stored procedure (which returns a result set)
and
> instead of returning the result set, just returning an empty result set, o
r
> in other words, what would be the column names of the result set?
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forum...server/200708/1
>[/vbcol]

Executing procedure for empty result set return

I am running SQL 2005, SP1.
Is there a way to execute a stored procedure (which returns a result set) and
instead of returning the result set, just returning an empty result set, or
in other words, what would be the column names of the result set?
Message posted via droptable.com
http://www.droptable.com/Uwe/Forums.aspx/sql-server/200708/1
you will have to modifiy the procedure.
add a paramter to it like @.IncludeResults and pass a 1 when you want
it to return results or a zero when you don't.
in the where clause of the final select statement in the procedure add
"And 1 = @.IncludeResults"
|||You can try SET FMTONLY ON.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"cbrichards via droptable.com" <u3288@.uwe> wrote in message news:767421b3eff7f@.uwe...
>I am running SQL 2005, SP1.
> Is there a way to execute a stored procedure (which returns a result set) and
> instead of returning the result set, just returning an empty result set, or
> in other words, what would be the column names of the result set?
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forums.aspx/sql-server/200708/1
>

Executing procedure for empty result set return

I am running SQL 2005, SP1.
Is there a way to execute a stored procedure (which returns a result set) and
instead of returning the result set, just returning an empty result set, or
in other words, what would be the column names of the result set?
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200708/1you will have to modifiy the procedure.
add a paramter to it like @.IncludeResults and pass a 1 when you want
it to return results or a zero when you don't.
in the where clause of the final select statement in the procedure add
"And 1 = @.IncludeResults"|||You can try SET FMTONLY ON.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"cbrichards via SQLMonster.com" <u3288@.uwe> wrote in message news:767421b3eff7f@.uwe...
>I am running SQL 2005, SP1.
> Is there a way to execute a stored procedure (which returns a result set) and
> instead of returning the result set, just returning an empty result set, or
> in other words, what would be the column names of the result set?
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200708/1
>

Executing a script and returning the results..

You know how osql can return the results of executing a .sql script to a window? Is there any way to achieve that same behavior in .NET? I am in the process of working on an application that needs to execute a .SQL script (which is easy to do) and then return the actual SQL output from the execution to a window. How do I get that output? None of the sql data objects seem to support this.I actually figured this one out. I can launch a process (like the command prompt) and then return the output through a stream.

ExecuteScalarProblem

 In my application Im inserting data into the database and returning the ID of the new record, to do this I have the following stored procedure: 

 
ALTER PROCEDURE Turbo_InsertAppChange(@.app_namevarchar(50),@.app_developerchar(3),@.app_rq_numchar(10),@.app_completition_datedatetime, @.app_descriptionvarchar(1500))AS SET NOCOUNT ON INSERT INTO Turbo_Change_Log(app_name,app_developer,app_rq_num,app_completition_date,app_description,date_entered)SELECT @.app_name, @.app_developer, @.app_rq_num,CONVERT(DATETIME,@.app_completition_date), @.app_description,GETDATE()SELECT SCOPE_IDENTITY()
 Where I use this procedure I have this code:  

 
Protected Sub EnterAppInfo()'##INSERT APP INFO## sSQL ="Turbo_InsertAppChange" Command =New SqlCommand(sSQL, Connection) Command.CommandType = CommandType.StoredProcedure Command.Parameters.Add("@.app_name", SqlDbType.VarChar).Value = application_name.Text.ToString Command.Parameters.Add("@.app_developer", SqlDbType.Char).Value = developer_list.SelectedValue.ToString Command.Parameters.Add("@.app_rq_num", SqlDbType.VarChar).Value = rq_num.Text.ToString Command.Parameters.Add("@.app_completition_date", SqlDbType.DateTime).Value =CType(Api_calendar1.DDate,Date) Command.Parameters.Add("@.app_description", SqlDbType.VarChar).Value = proj_desc.Text.ToStringTry Connection.Open()Dim NewIdAs Integer =CType(Command.ExecuteScalar(),Integer)
 

...

But I'm getting theObject reference not set to an instance of an object error at theDim NewId As Integer = CType(Command.ExecuteScalar(),Integer) line. When I run this procedure alone in QueryAnalyzer it returns the ID like its supposed to, but when I run it in my application I get the above error. What am I doing wrong here?

ExecuteScalar return null reference if the result set is empty.

So you convert a null object to integer which cause the error:

CType(Command.ExecuteScalar(),Integer)

You can catch the exeption and return 0(means null).

ExecuteScalar() Not Returning Value?

Okay so here's a wierd one. I use SQLYog to peek into/administrate my databases.

I noticed that this chunk of code is not producing a value...

Using ConnAs New MySqlConnection(Settings.MySqlConnectionString)Using CmdAs New MySqlCommand("SELECT COUNT(*) FROM tbladminpermissions WHERE (PermissionFiles LIKE'%?CurrentPage%') AND Enabled=1", Conn)With Cmd.Parameters.Add(New MySqlParameter("?CurrentPage",thisPage))End WithConn.Open()Exists = Cmd.ExecuteScalar()End UsingEnd Using

Exists is declared outside of that block so that other logic can access it. thisPage is a variable declared outside, as well, that contains a simple string, like 'index.aspx'. With the value set to 'index.aspx' a count of 1 should be returned, and is returned in SQLYog.

SELECTCOUNT(*)FROM tbladminpermissionsWHERE (PermissionFilesLIKE'%index.aspx%')AND Enabled=1

This produces a value of 1, but NO value at all is returned from Cmd.ExecuteScalar(). I use this method in MANY places and don't have this problem, but here it rises out of the mist and I can't figure it out. I have no Try/Catch blocks so any error should be evident in the yellow/red error screen, but no errors occur in the server logs on in the application itself.

Does anybody have any ideas?

Try

WHERE (PermissionFiles LIKE'%' + ?CurrentPage + '%')

Jos

|||

That didn't give me the desired result, either. It started returning "every" row that met all criteria but theCurrentPage.

But, thanks to your suggestion, what I ended up with was...

Using ConnAs New MySqlConnection(Settings.MySqlConnectionString)Using CmdAs New MySqlCommand("SELECT COUNT(*) FROM tbladminpermissions WHERE (PermissionFiles LIKE'%" & thisPage & "%') AND Enabled=1 AND Everybody=0", Conn)Conn.Open()Exists = Cmd.ExecuteScalar()End UsingEnd Using

Which works "as intended". Thanks!|||

execute scalar returns firts column from firts row of returned data so use this:

SELECT (SELECTCOUNT(*)FROM tbladminpermissionsWHERE (PermissionFilesLIKE'%index.aspx%')AND Enabled=1)

you can also use:

ifexists(SELECT *FROM tbladminpermissionsWHERE (PermissionFilesLIKE'%index.aspx%')AND Enabled=1)

select 1

else

select 0

which can work faster if you have more than one record whcih meet your criteria

I hope that it will work

sql

ExecuteNonQuery not returning rows affected

I was racking my brains trying to figure out why SomeCommand.ExecuteNonQuery() was not returning any rows...

SQL Server 2005 likes to put theSET NOCOUNT ON statement in every stored procedure you write. By hiding the count of records touched by your query, you also disable the results to be consumed by your application.

So I don't recommend using this statement for your stored procedures and ASP.NET applications, as this functionality is fairly critical for error trapping.

Thanks for your help...

But can u tell me what is the use of SET NOCOUNT ON ?

|||You use it to suppress the counts of records affected in your stored procedure. There must also always be a resultset that is returned for each such message. By turning nocount on, then the records affected counts aren't transferred to the client, and empty resulsets are discarded.