Stored Procedure with Schema Table Name Variables

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Stored Procedure with Schema Table Name Variables

Author

Topic

RBC
Starting Member

17 Posts

Posted - 2008-12-30 : 19:05:08

Hi,

Could you please help me with this SP?

The SP returns the rows to be displayed in the grid view.

Actually very nice; it returns the number of rows needed for your grid view; not all of the rows in the table.

I like to have the schema and the table name variable…okay, everything as a variable. And use the Stored Procedure for all my Grid Views.

I have syntax error form my SchemaTableName variable. Plus I wonder if I have to specify the lenght of the NVARCHAR?

Thank you,

Rune

CREATE PROCEDURE [SchemaName].[ SPName]

(

@vcs_FROM_SchemaTableName_string TABLE,

@vcs_FieldID_string NVARCHAR,

@vcs_ORDER_BY_string NVARCHAR,

@vcs_SELECT_Field_B_string NVARCHAR,

@vmb_RowIndexStart_A INT,

@vmb_RowPerPage_B INT,

@vmb_RowTableTotal_C_out INT OUTPUT

)

AS

DECLARE @vsp_RowFirstID_A NVARCHAR

SET ROWCOUNT @vmb_RowIndexStart_A

SELECT @vsp_RowFirstID_A = @vcs_FieldID_string

FROM @vcs_FROM_SchemaTableName_string

ORDER BY @vcs_ORDER_BY_string

SET ROWCOUNT @vmb_RowPerPage_B

SELECT @vcs_SELECT_Field_B_string

FROM @vcs_FROM_SchemaTableName_string

WHERE @vcs_FieldID_string >= @vsp_RowFirstID_A

ORDER BY @vcs_ORDER_BY_string

SET ROWCOUNT 0

SELECT @vmb_RowTableTotal_C_out = COUNT(@vcs_FieldID_string)

FROM @vcs_FROM_SchemaTableName_string

====================

ORIGINAL

CREATE PROCEDURE [dbo].[usp_PageResults_Products]

(

@startRowIndex int,

@maximumRows int,

@totalRows int OUTPUT

)

AS

DECLARE @first_id int, @startRow int

-- Get the first employeeID for our page of records

SET ROWCOUNT @startRowIndex

SELECT @first_id = ProductID FROM Products where Discontinued = 0 ORDER BY ProductID

-- Now, set the row count to MaximumRows and get

-- all records >= @first_id

SET ROWCOUNT @maximumRows

SELECT ProductID,ProductName,Discontinued from Products

WHERE Discontinued = 0 and ProductID >= @first_id

ORDER BY ProductName

SET ROWCOUNT 0

Select @totalRows = COUNT(ProductID) FROM Products where Discontinued = 0

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-12-30 : 20:27:22

This is a very bad idea. It's lazy coding. It is bad for performance and security reasons.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

RBC
Starting Member

17 Posts

Posted - 2008-12-30 : 20:46:29

Hi Tara,

Thank you very much for your feedback.
What is your suggestion for retriving data from the database to the GridView?
I thought this idea was good instead of retriving 4000 rows and showing 10.
I need high performance and I am not lazy :); please show me the code, I am ready to work!

Thank you,
Rune

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-12-30 : 22:27:01

Your idea of paging the data is a very good one. The part that is bad is using variables for the table names, order by, etc... That can only be achieved through dynamic SQL, which is bad for performance and security reasons. Create a stored procedure for each thing you need to page through data.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

RBC
Starting Member

17 Posts

Posted - 2008-12-31 : 08:56:52

Yes, I liked the paging too...pretty nice!
I assume you are right regarding the variables for the table name, etc.
But could you please help me with the code to compile and I will test the performance.
Thank you,
Rune

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-31 : 09:12:09

[code]
CREATE PROCEDURE [SchemaName].[ SPName]

(

@vcs_FROM_SchemaTableName_string NVARCHAR(1000),

@vcs_FieldID_string NVARCHAR(1000),

@vcs_ORDER_BY_string NVARCHAR(1000),

@vcs_SELECT_Field_B_string NVARCHAR(1000),

@vmb_RowIndexStart_A INT,

@vmb_RowPerPage_B INT,

@vmb_RowTableTotal_C_out INT OUTPUT

)

AS

DECLARE @vsp_RowFirstID_A NVARCHAR(l00),@Sql nvarchar(max),@Params nvarchar(2000)

SET ROWCOUNT @vmb_RowIndexStart_A
SET @Params='@vcs_FieldID_string NVARCHAR(1000),@vcs_FROM_SchemaTableName_string NVARCHAR(1000),@vsp_RowFirstID_A NVARCHAR(l00) OUTPUT'
SET @Sql='SELECT @vsp_RowFirstID_A = @vcs_FieldID_string

FROM '+@vcs_FROM_SchemaTableName_string +'

ORDER BY @vcs_ORDER_BY_string'

EXEC sp_executesql @Sql,@Params,@vsp_RowFirstID_A =@vsp_RowFirstID_A OUTPUT,@vcs_FieldID_string=@vcs_FieldID_string,@vcs_FROM_SchemaTableName_string=@vcs_FROM_SchemaTableName_string

EXEC('SET ROWCOUNT ' + @vmb_RowPerPage_B)

EXEC('SELECT '+@vcs_SELECT_Field_B_string +

' FROM '+@vcs_FROM_SchemaTableName_string+

' WHERE '+@vcs_FieldID_string +' >= '+@vsp_RowFirstID_A +

' ORDER BY '+@vcs_ORDER_BY_string

SET ROWCOUNT 0

SET @Sql='SELECT @vmb_RowTableTotal_C_out = COUNT(@vcs_FieldID_string)

FROM '+@vcs_FROM_SchemaTableName_string

SET @Params='@vcs_FROM_SchemaTableName_string NVARCHAR(l00),@vcs_FieldID_string NVARCHAR(1000),@vmb_RowTableTotal_C_out INT OUT'

EXEC sp_executesql @Sql,@Params,@vmb_RowTableTotal_C_out =@vmb_RowTableTotal_C_out OUTPUT,@vcs_FieldID_string=@vcs_FieldID_string,@vcs_FROM_SchemaTableName_string=@vcs_FROM_SchemaTableName_string
[/code]

RBC
Starting Member

17 Posts

Posted - 2008-12-31 : 11:11:14

THANK YOU!!!

But I get following error..., I am running SQL Server 2005...:

Msg 102, Level 15, State 1, Procedure dSP_VIEW_RowPerPage, Line 25
Incorrect syntax near 'l00'.
Msg 137, Level 15, State 1, Procedure dSP_VIEW_RowPerPage, Line 28
Must declare the scalar variable "@Params".
Msg 137, Level 15, State 1, Procedure dSP_VIEW_RowPerPage, Line 29
Must declare the scalar variable "@Sql".
Msg 137, Level 15, State 2, Procedure dSP_VIEW_RowPerPage, Line 35
Must declare the scalar variable "@Sql".
Msg 137, Level 15, State 2, Procedure dSP_VIEW_RowPerPage, Line 45
Must declare the scalar variable "@vsp_RowFirstID_A".
Msg 137, Level 15, State 1, Procedure dSP_VIEW_RowPerPage, Line 53
Must declare the scalar variable "@Sql".
Msg 137, Level 15, State 1, Procedure dSP_VIEW_RowPerPage, Line 57
Must declare the scalar variable "@Params".
Msg 137, Level 15, State 2, Procedure dSP_VIEW_RowPerPage, Line 59
Must declare the scalar variable "@Sql".

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-12-31 : 12:30:04

visakh, why are you posting code like that? As top posters on this site, we shouldn't just post answers that you wouldn't recommend using. Let's work through this problem without posting inefficient code and bad code.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

RBC
Starting Member

17 Posts

Posted - 2008-12-31 : 15:07:20

Tara,

It has to be the people choices to select their own code.
Are you going to be the person who decides which code I am going to run in my software? Are you a software police? :) LOL
I appreciate your comments; but I want to make my own choice after my own testing.
And if you see the code is bad and inefficient; please produce the excellent code...I am still waiting.
The code I have now run to the server and select 4000 rows for every time you page the grid view; I assume the code to retrieve 10 at the time if more efficient. We agree till now...
Now I like to test a SP which you input the parameters.
If the code is inefficient I will not implement the code; promise...

Rune

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-12-31 : 15:21:32

It is our responsibility to provide good code. We are not here just to add to our post count which it appears is what is happening when solutions like that are offered. Remove the dynamic SQL from your code and it'll more efficient. Do not use variables for the ORDER BY, table names, etc..., write multiple stored procedures instead.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

RBC
Starting Member

17 Posts

Posted - 2008-12-31 : 15:44:01

Tara,

If I want to write one (1) or multiple (n) Stored Procedures; it will be my decision.
I am not so happy you give shit to people who try to help me…
If it is your responsibility to provide good code, which is very nice; it will be my decision in the end what is good for me.
If you do not manage to help me; please stop writing.

Thank you,
Rune

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-12-31 : 16:47:42

Yes it will definitely be your decision.

How big is your database? Is performance a consideration here?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

RBC
Starting Member

17 Posts

Posted - 2009-01-03 : 11:27:22

Hi Tara,

Happy New Year!

Thank you very much for your feedback.
I have no performance problem; it is a mini database with few tables and with little data.

Thank you,
Rune

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-01-03 : 11:46:23

If it's tiny, then dynamic SQL shouldn't impact you. I support large databases, so this is something that absolutely could not be implemented in production. I would lose my job if I allowed it as customers would be screaming about how long each page would take to load.

Happy New Year to you too!

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-01-03 : 11:56:30

One last bit of info...

Is there a reason why you are using a stored procedure for your data access? A stored procedure like this provides no benefits, so you should just use inline SQL in your application.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2009-01-03 : 12:34:58

quote:
Originally posted by tkizer

One last bit of info...
Is there a reason why you are using a stored procedure for your data access? A stored procedure like this provides no benefits, so you should just use inline SQL in your application.

If you take tkizer's suggestion and move the query to your application, and if you are using .Net 3/3.5, LINQ to SQL is ideally suited for this - it has concise and efficient syntax for retrieving and paging through data from SQL databases. The book "LINQ in Action" (and many other books on LINQ) have examples of doing precisely this.

But, I am veering off from the topic of this forum, so I will shut up. I have to warn you though, you may find LINQ so elegant and beautiful and may get addicted to it, so beware... :--)

RBC
Starting Member

17 Posts

Posted - 2009-01-03 : 13:15:39

NICE!
What will be the inline SQL to select 10 rows with a start value.
Issue (1) is that I have 100 rows and I like to pick 10 by paging: << <> >>
Issue (2) is that the 100 rows can be sorted, so I need to select the 10 rows from the right sort order. (Can we select 10 rows by using the index of the table?)
Next version I will use LINQ and SilverLight :)
Please notice the original SP has error, present SP works; because the table Products is ASC with the ProductID
Issue (3) if PK is not IDENTENTY; EXAMPLE nvarchar the SP doesn't work. My PK is nvarchar...of course.
What I am asking for is as follow: SP or inline SQL (LINQ sample:) with possibility to select 10 rows by different sort orders with a start position in the table

CREATE PROCEDURE [dbo].[usp_PageResults_Products]

(
@startRowIndex int,
@maximumRows int,
)

AS

DECLARE @first_id int
SET ROWCOUNT @startRowIndex
SELECT @first_id = ProductID
FROM Products
where Discontinued = 0
ORDER BY ProductID

SET ROWCOUNT @maximumRows
SELECT ProductID,ProductName,Discontinued from Products
WHERE Discontinued = 0 and ProductID >= @first_id
ORDER BY ProductID

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-03 : 14:00:21

quote:
Originally posted by tkizer

visakh, why are you posting code like that? As top posters on this site, we shouldn't just post answers that you wouldn't recommend using. Let's work through this problem without posting inefficient code and bad code.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

how many times have we seen cases where we give a better method and they come back saying either they cant do like that because of some restrictions or they are told to use like this by their boss. thats why i tried to help OP in way the code he gave rather than i making changes on his approach.

RBC
Starting Member

17 Posts

Posted - 2009-01-03 : 17:43:01

Hi visakh16,

I agree with you; finish the question asked. (SP with parameters)

...Please notice the error in the SP was discovered by me after several hours of testing; but this has noting to do with the question asked from the beginning.

I will open a new case with paging.

Please do not worry about the answer when you answer a question; only point out the side effects…

People smoke even you have deadly warning on the cigarette package; in the end it will be the people choice…

Thank you,
Rune

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-01-03 : 19:35:48

quote:
Originally posted by visakh16

quote:
Originally posted by tkizer

visakh, why are you posting code like that? As top posters on this site, we shouldn't just post answers that you wouldn't recommend using. Let's work through this problem without posting inefficient code and bad code.

how many times have we seen cases where we give a better method and they come back saying either they cant do like that because of some restrictions or they are told to use like this by their boss. thats why i tried to help OP in way the code he gave rather than i making changes on his approach.

I don't post answers just for the sake of what their boss wanted. There's a few of us here who try to lead the OP down the correct path and with great success sometimes. Unfortunately though not everyone is willing or able to follow our experienced opinions. Where's Jeff to help out on this one?!

There should be no reason to use a stored procedure like this. It provides zero benefits over all other methods. Inline sql would be the better alternative here.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-01-03 : 19:38:12

RBC, you should check out the dynamic ORDER BY article: http://www.sqlteam.com/article/dynamic-order-by

It shows how to do dynamic sorting without using dynamic SQL. This would be used if you are still going down the stored procedure approach. Make sure to read the article's comments as you'll need to check those out for the different data type issues/solutions.

By the way, inline sql just means that you send the actual queries from your application rather than just the stored procedure call.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources