Running procedure but without as stored procedure

Question

0.00/5 (No votes)

See more:

Hi,

I have following stored procedure in database which is used for paging purpose for a gridview. it inserts records in a temporary table, sets rowcount and just returns specific rows.

procedure [dbo].[GetResults]
@pIndex varchar(50),
@startRowIndex int,
@maximumRows int 
AS
DECLARE @TempItems TABLE
(
ID int IDENTITY,
GMKey int,
Details nChar(800)
)
DECLARE @maxRow int

SET CONCAT_NULL_YIELDS_NULL OFF
SET ANSI_WARNINGS OFF

SET @maxRow = (@startRowIndex + @maximumRows) - 1 
SET ROWCOUNT @maxRow

INSERT INTO @TempItems (GMKey,TABLENAME,Details)
Select PRIMARYINDEX as GMKey,  LTRIM(RTRIM(cast(Facility_Name as nChar(800)))) as Details 
from SEARCHINFO where Facility_Name LIKE @pIndex + '%' order by PRIMARYINDEX DESC

SET ROWCOUNT @maximumRows
SELECT GMKey, TABLENAME, Details FROM @TempItems WHERE ID >= @startRowIndex

RETURN

Our requirement is not to have stored procedure in the database, this functionality has to be implemented through asp.net code. I am not sure how to achieve this using a normal command object. As you can note there are statements as SET ROWCOUNT, create temporary table etc. Can I use the script of the stored procedure and give as commandtext to command object and execute it? or is there any other better option.

Thanks

Vijay

Posted 21-Sep-10 3:37am

vjvjvjvj

Updated 21-Sep-10 3:53am

HimanshuJoshi

v2

Add a Solution

Comments

HimanshuJoshi 21-Sep-10 9:53am

Edited for code-block

Hiren solanki 21-Sep-10 9:54am

You should have only way to give command text to command object.

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Al-Farooque Shubho · Answer 1 · 2010-09-21T04:05:00

The Stored Procedure is nothing but a pre-complied SQL script which is stored as a named object and can be invoked by the name. Whatever SQL is written in the stored procedure, can be executed as an SQL srcipt (Given the fact that, parameter values are provided in the script)

So, you can of course build the appropirate SQL script in your application (With the parameter values) and execute as a command text (Not as a Stored Procedure) via the command object. But, you might need to adjust your Ado.net specific application codes so that, it can obtain value from the result set, instead of reading the output parameters if there is any.

By the way, use of SCOPE_IDENTITY() is a better option for getting the newly inserted ID of the table. You can Google it to learn how to use it.

vjvjvjvj · Answer 2 · 2010-09-21T05:05:00

My basic requirement is perform a search on a table e.g. "where facility_name like '<param />%' and if the query returns 100 records but my gridview is supposed to show second page i.e. it is supposed to show 6 - 10 from the returned dataset of 100 records. Can we not avoid the script above and instead just give a simple sql query which would return only 6 to 10 rows (i.e. only 5 rows). The script above does that.

Running procedure but without as stored procedure

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0