Tweet
I know this is crazy, but I've been thinking about it for a while. In the past I've talked about stored procedures with clauses taken from another table. Ok, I understand that this is difficult to explain. Let me try.
Let's say I have a table that contains all the company departments. I actually use this table to create parameters for a where-clause in a stored procedure. That is done because I need to change departments without having to change the stored procedure. When a new department is inserted into the departments table, the stored procedure works immediately, without the need to change it.
Said that, please follow me. Let's say I have 3 stored procedures that perform queries on different tables with appropriate joins. Part of the code of these 3 stored procedures is exactly the same. When I have to change that part of the code, I have to change it 3 times.
Would it be crazy if I put that part of the code in a table, fetch it, and use it in the stored procedure?
In my SQL Server database, I have more than 3 stored procedures in that situation. I understand that it is absolutely possible - in coding terms - to do what I said. What really worry me is: would it pose some performance issue?
I really would like if you share your thoughts on this.
Web development, web design and programming.
Showing posts with label stored procedure. Show all posts
Showing posts with label stored procedure. Show all posts
Wednesday, 2 March 2011
Wednesday, 2 February 2011
SQL Server: Stored Procedures and variables problem
Tweet
In my last post I published a step-by-step guide to create Stored Procedures in SQL Server using SQL Server Management Studio.
There are some things I would like to add, because in my experience I sometimes got stuck with errors that - when solved - were so easy to pinpoint, while little information can be found in tech forums or generally in tech sites. Specifically there is a common problem with the @query variable and its length.
In my last post I published a step-by-step guide to create Stored Procedures in SQL Server using SQL Server Management Studio.
There are some things I would like to add, because in my experience I sometimes got stuck with errors that - when solved - were so easy to pinpoint, while little information can be found in tech forums or generally in tech sites. Specifically there is a common problem with the @query variable and its length.
Monday, 31 January 2011
SQL Server: How to create Stored Procedures
Tweet
"How do I create Stored Procedures?" ... ... ok, I know, this is very basic, but do not feel ashamed if you don't know the answer. When I started playing with SQL Server, Stored Procedures seemed to me a secret and foreign land where everything is difficult and hard to understand. Well, I must say, it is not. So get ready, put on your armor and let's go to slay the dragon (a.k.a. the Stored Procedure)!
"How do I create Stored Procedures?" ... ... ok, I know, this is very basic, but do not feel ashamed if you don't know the answer. When I started playing with SQL Server, Stored Procedures seemed to me a secret and foreign land where everything is difficult and hard to understand. Well, I must say, it is not. So get ready, put on your armor and let's go to slay the dragon (a.k.a. the Stored Procedure)!
Friday, 12 November 2010
SQL Server: How to deal with Acucobol date fields
Tweet
As explained in a previous post, you can connect your Sql Server to an Acucobol db using AcuODBC. In the last part of the article I explained that there are performance issues when you query a linked server with OpenQuery and that it is better to import relevant data from the linked server to a Sql Server table in order to benefit from all the features available through stored procedures, triggers and so on.
I run some queries overnight using SQL Scheduler to keep my local tables updated against the Acucobol tables, but, when importing the data, in some way I manipulate them to make them easier to query. What I found important in terms of performance, is to convert date fields from int to smalldatetime format. Furthermore, when importing data I insert into the newly created Sql Server table an identity field and set it as primary key.
Let's break down the query and I will explain it bit by bit.
As explained in a previous post, you can connect your Sql Server to an Acucobol db using AcuODBC. In the last part of the article I explained that there are performance issues when you query a linked server with OpenQuery and that it is better to import relevant data from the linked server to a Sql Server table in order to benefit from all the features available through stored procedures, triggers and so on.
I run some queries overnight using SQL Scheduler to keep my local tables updated against the Acucobol tables, but, when importing the data, in some way I manipulate them to make them easier to query. What I found important in terms of performance, is to convert date fields from int to smalldatetime format. Furthermore, when importing data I insert into the newly created Sql Server table an identity field and set it as primary key.
Let's break down the query and I will explain it bit by bit.
Monday, 30 August 2010
How to execute Stored Procedures from ASP
Tweet
The power of Stored Procedures in SQL Server is quite useful when developing web applications. It is sure that using such power in your code is very important in order to be sure the queries are executed quickly and efficiently. But, how do you execute those queries from asp? Actually it is very easy as soon as you understand the logic behind it. I've seen many web site and blogs explaining that, however it seems that a simple and straight way of showing the method is missing. Here we go.
The power of Stored Procedures in SQL Server is quite useful when developing web applications. It is sure that using such power in your code is very important in order to be sure the queries are executed quickly and efficiently. But, how do you execute those queries from asp? Actually it is very easy as soon as you understand the logic behind it. I've seen many web site and blogs explaining that, however it seems that a simple and straight way of showing the method is missing. Here we go.
Friday, 27 August 2010
SQL Server utilities: SSMS Tools Pack
Tweet
Working with SQL Server sometimes is really boring: you need to write a lot of stuff and - as an almost-without-memory person as I am - remember 'how were those stored procedures done'. I know very expert people who use a simple list of cleverly named txt file, where they store all the knowledge. So when a specific function/procedure is needed, they look into that knowledge base, copy and paste. That's handy, and - as humans - quite helpful.
If you use Microsoft SLQ Server Management Studio to do your database development and maintenance, you might find the SSMS Tools Pack very useful. I've used the tool for some time and I really don't think I can do without it now.
Working with SQL Server sometimes is really boring: you need to write a lot of stuff and - as an almost-without-memory person as I am - remember 'how were those stored procedures done'. I know very expert people who use a simple list of cleverly named txt file, where they store all the knowledge. So when a specific function/procedure is needed, they look into that knowledge base, copy and paste. That's handy, and - as humans - quite helpful.
If you use Microsoft SLQ Server Management Studio to do your database development and maintenance, you might find the SSMS Tools Pack very useful. I've used the tool for some time and I really don't think I can do without it now.
Subscribe to:
Posts (Atom)