SP Return 2 recordset !!! Need help | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

SP Return 2 recordset !!! Need help

Hi, i need to create a store procedure to allow client program to pass in a query and return a set of record back to client program as below //////// Procedure start /////////////////
Declare @vcSQL nvarchar(1000)
Declare @iRowReturn int Set @vcSQL = N’Select UserCode from UserProfile Where UserCode =’ + ”’Admin”’ — Execute a Query and see whether got data or not
EXEC sp_executesql @vcSQL Set @iRowReturn = @@RowCount — if no record found, need to change to ‘Like’ statement
if @iRowReturn = 0 begin
Set @vcSQL = N’Select UserCode from UserProfile Where UserCode like ‘ + ”’Admin%”’
EXEC sp_executesql @vcSQL
end //////// Procedure end ///////////////// when i run above statement in management studio, it return 2 set of data to me, the 1st recordset is empty and second record set contain some data. In my program, i always get the 1st record set instead of 2nd record set which return by ‘like’ statement. Is there anyway to hide or remove the 1st record set which is empty so my program can get the ‘like’ record set. if a data found from ‘=’ query, it only return 1 recordset, this is fine for me. Or can someone suggest other way to do this ?
create a temp table structure as the result of the first Stored Procedure and direct the result of the SP into the temp table
create table #temp
(
. . .
) insert into #temp( . . . ) EXEC sp_executesql @vcSQL KH
this procedure will be use by different table, eg Country, Currency, User, Customer. Table structure is not same among them so how can i create a temp table to keep the record ? What i need to do is create a general seach store procedure and can apply to any table.
but for your query here, the result will always be the same right ?
Declare @vcSQL nvarchar(1000)
Declare @iRowReturn int create table #temp
(
UserCode varchar(100)
)
Set @vcSQL = N’Select UserCode from UserProfile Where UserCode =’ + ”’Admin”’ — Execute a Query and see whether got data or not
insert into #temp(UserCode) EXEC sp_executesql @vcSQL Set @iRowReturn = @@RowCount — if no record found, need to change to ‘Like’ statement
if @iRowReturn = 0 begin
Set @vcSQL = N’Select UserCode from UserProfile Where UserCode like ‘ + ”’Admin%”’
EXEC sp_executesql @vcSQL
end
KH
above just an example, how about the procedure look like : //////// Procedure start ///////////////// create pro dbo.cpSQLLookup(@vcSelectQuery nvarchar(1000),
@vcLikeQuery nvarchar(1000) )


Declare @iRowReturn int — Execute a Query and see whether got data or not
EXEC sp_executesql @vcSelectQuery Set @iRowReturn = @@RowCount — if no record found, need to change to ‘Like’ statement
if @iRowReturn = 0 begin
EXEC sp_executesql @vcLikeQuery
end //////// Procedure end ///////////////// from above example, how you know what colum are in the query ? i can pass in 1: Select CustCode, CustName from CustomerTable Where….
2: Select InvoiceNo, InvoiceDate, InvoiceAmount, InvoiceCurrency from InvoiceHeader Where.
3: Select CountryCode, CurrencyCode, CountryName from CountryTable Where…
….. in this case, we still able to use temp table ?
Why can’t you quote a search SP for each of the table ? Using Dynamic SQL might save you few SPs but there are also disadvantages like SQL Injection Take a look here
http://www.sommarskog.se/dynamic_sql.html
http://www.sql-server-performance.com/dm_acunetix_wvs.asp
http://www.sqlservercentral.com/columnists/RDyess/sqlinjectionpart1.asp
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=77475
KH
thank you for your info. for SQL injection, it is more on programmer adding the quote string before and after the user input value but in my life of developer, i nerver do this. When i become a programmer, my senior 1st thing told me never, never do this and need to use a function to add quote to the user input. I am using Borland Developer Studio, in Borland library, there is a function call QuotedStr(s: string) to add quote to a string. inside the function, it already take care the SQL injection. i have around 50 master file ( like country, currency, port… ), in this case, i will need to create more than 50 sp in database just for this purpose ? and in my program, different screen might need to send different query to the same master file ( need to display different field and ‘where’ clause also different eventhoug they are select from same master table ). For other disavantage, i aware of it and i have no choice because i am building n-tier application and my client need to use internet to access HQ Application server. when a user try to search a value like entering ‘M’ in edit box, following will happend:
1) Send a query from client program to server (using internet line) to check whether this code is exists or not, if yes, bring back the description or name for that code.
2) if not exists ( recordset is empty after open ), then fire another query to retireve the possible record ( which prefix is same to that code, in this case, use like ‘xx%’) to return a list of record which prefix are same to what user key in. just immaging, the search/browse function is building in this way and the system always need to send 2 query in order to do it, if i can minimize 2 query into 1 ( using dynamic sql run in store procedure ), then i don’t need to fire second query. This system is not running in local LAN, the speed is very important, one way to improve the speed is reduce the trip to server. so i try look into whether got a way to do it.

use pubs declare @snvarchar(4000),
@s1nvarchar(4000),
@s2nvarchar(4000),
@cint [email protected] = N’select emp_id, fname, lname from employee where lname = ”Lars”’
[email protected] = N’select emp_id, fname, lname from employee where lname like ”Lars%”’ [email protected] = N’select @c = count(*) from (‘ + @s1 + ‘)c’
exec sp_executesql @s, N’@c int output’, @c output if @c <> 0
begin
exec sp_executesql @s1
end
else
begin
exec sp_executesql @s2
end KH
yes…..thank you very much ! [<img src=’/community/emoticons/emotion-1.gif’ alt=’:)‘ />]<br /><br /><br /><blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by khtan</i><br /><br /><pre id="code"><font face="courier" size="2" id="code"><br />use pubs<br /><br />declare @snvarchar(4000),<br />@s1nvarchar(4000),<br />@s2nvarchar(4000),<br />@cint<br /><br />[email protected] = N’select emp_id, fname, lname from employee where lname = ”Lars”'<br />[email protected] = N’select emp_id, fname, lname from employee where lname like ”Lars%”'<br /><br />[email protected] = N’select @c = count(*) from (‘ + @s1 + ‘)c'<br />exec sp_executesql @s, N’@c int output’, @c output<br /><br />if @c &lt;&gt; 0 <br />begin<br />exec sp_executesql @s1<br />end<br />else<br />begin<br />exec sp_executesql @s2<br />end<br /></font id="code"></pre id="code"><br /><br /><hr noshade size="1"><br /><font color="blue"><font size="1"><i>KH</i></font id="size1"></font id="blue"><br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote">
]]>