Simple Way To Build This Stored Procedure
May 29, 2008
Hi I have a table that has several related keys into other tables with the main table looking like
System ID buildingID roomID ColorID
1 prikey 3 4 5
2 2 1 3
building table looks like
buildingID building name
1 prikey Flower bld
2 Lab bld
3 Crafts bld
4 Eng bld
room table looks like
roomID room name
1 prikey 100
2 101
3 102
Color table looks like
colorID Color name
1prikey red
2 blue
3 grey
4 white
I will need to pull the data from the system table based building name, room name and color. If the parameter is zero I will not filter on that parameter so will only be filtering on one parameter for each time the procedure is called. Any ideas on a simple query, thanks!
View 4 Replies
ADVERTISEMENT
Feb 21, 2007
I have a few textboxes on a page that I would like to use as a search page and have clients shown in a gridview that meet the users entry into one or more of the textboxes.
I have ClientID, LastName, FirstName, Address, and Keywords. How would I build a stored procedure to allow me to do this?
View 5 Replies
View Related
Jun 6, 2008
My existing ASP 1.0 site keeps getting hacked using SQL injections. I have rewritten the site in ASP 3.5 to stop the attacks but cannot figure out how to dynamically generate a basic keyword search.
I am trying to take the keywords entered into an array and then construct the WHERE clause - not having much luck. Getting either errors or double LIKE statements. Need some help.
string[] SqlKWSrch;
SqlSrch = KWordSrch.Text;SqlKWSrch = SqlSrch.Split(' ', ','); int AStop = SqlKWSrch.Length; int i = 0; foreach( string a in SqlKWSearch ) { if (i <= AStop) { SqlWHR = SqlWHR + "L_Kwords LIKE '%' + " + " '" + SqlKWSrch[i] + "' " + " + '%' AND "; } else { SqlWHR = SqlWHR + "L_Kwords LIKE '%' + " + " '" + SqlKWSrch[i] + "' " + " + '%' "; } i++; }
1) I can't seem to properly terminate the final LIKE statement2) can't figure out how to pass 'SqlWHR' to the procedure
GIVEN KEYWORDS: 'antique chairs' entered I want to end up with the below SP, the @SqlWHR parameter appeared to have worked once but it probably was an illusion.
PROCEDURE KeyWordSearch@SqlWHR varchar(100)AS
SELECT L_Name, L_City, L_State, L_Display FROM tblCompanies WHERE L_Kwords LIKE '%' + 'antique' + '%' AND L_Kwords LIKE '%' + 'chairs' + '%' AND L_Display = 1
RETURN
Thank you
View 5 Replies
View Related
Nov 19, 2007
I have SQL table (tblUsers) and one of the fields holds the email address. I want to step through each record build a multiple email string to send to a lot of people. It would look like this
Str_email = Me@hotmail.com;Andy@Hotmail.com;Fred@Hotmail.com
I then want to pass Str_email back to an asp.web page
Can this be done in a stored procedure ?
View 5 Replies
View Related
Apr 9, 2014
OK, to simplify some annual auditing of DB users (not the SQL logins,) I'm trying to craft a stored procedure that the customer on the server (they're the only customer on this particular server) can run to get a listing of all DB users and what roles they have.
I've got a query that returns this for the currently selected DB, so that part's done.I can use SP_MSFOREACHDB to run it against each DB, with the results going into a temp table to make it easier to copy/paste into an Excel file.What I want to do, and can't seem to see how, is wrap the whole thing in yet another SP of my own, with an EXECUTE AS so that the customer doesn't need sysadmin or any special privileges on the server. When I do this, it runs, but only against master.
Now, from digging it looks like you can't have an "insert #temptable exec sp_whatever" inside another SP. I'd like to avoid dynamic SQL, and while I know there are problems with MSFOREACHDB, it'll work for what we need.how to turn a user created SP, into a system SP so it can be run regardless of the DB you've selected, so at least there's that.
View 7 Replies
View Related
Jun 17, 2015
Is it possible to force a build to fail when a stored procedure in a project calls another stored procedure with one or more required parameters missing. E.g.:
Â
CREATE PROCEDURE [App].[ServiceUser_Save]
@userID int-- Param #0
,@serviceuserID int-- Param #1
,@version int-- Param #2 etc...
And then in a separate stored procedure we have the following
CREATE PROCEDURE [Admin].[CreateMiscellaneousData]
@customerIDint,
@serviceIDint,
@fullURL nvarchar(255),
@apiUserPwd nvarchar(255)
AS
BEGIN
...
EXEC@return_value = [App].[ServiceUser_Save]
@userID = 1,
@serviceuserID = 0, etc...
Note there is no value passed for the @Version parameter.
What I want is the build to fail because of the missing parameter on the call to ServiceUser_Save in the Create_MiscellaneousData stored procedure.
How can I achieve this?
View 4 Replies
View Related
Mar 3, 2008
Hello Everyone,I am sure, for the knowing, that this is a simple question.I have a table called tblTickets and a table called tblProjects. I would like to make a stored procedure that does the followingCopy entire contents of tblTickets record to tblProjects record and also changes tblTickets.sDetails to contain some text like "This ticket has been escalated into a project click here to view the project"tblProjects.sDetails would remain the same as the original.tblProjects will have additional fields but they can remain blank for the time being.ThanksMatt
View 11 Replies
View Related
Jun 3, 2008
What I am doing:I am in the process of creating a "log" that records when I update a record. I would like a new row to be inserted into BookLog each time a book's information is updated. Below I have the "update" code and below that is the code that is suppose to be inserting the newely updated record in the BookLog table.
**What the problem is**: I've tested the insert code, everything works EXCEPT BookEntryDate. I do not want to update the BookEntryDate when I am inserting it into BookLog. I just want to keep the original entry date from the Books table thats already there. In the BooksLog table I have "BookEntryDate" to not allow nulls...when i go to test i get an error that says "Procedure 'BookUpdate' expects parameter '@BookEntryDate', which was not supplied." I've tried several things and I am stuck :-/ I need some ideas. Thanks for your help!Alter Procedure dbo.BookUpdate@BookEntryDate datetime,@BookSummary nvarchar(1000),@BookComment nvarchar(50),@UserID nvarchar(50),@BookID bigint AsDeclare @Now datetimeSet @Now = GetDate()Update BooksSet Books.BookSummary = @BookSummary,Books.UserID = @UserID,Books.BookComment = @BookCommentWhere Books.BookID = @BookIDINSERT INTO BookLog(BookID,BookEntryDate,BookSummary, UserID,BookCommentLogDate)VALUES (@BookID,@BookEntryDate, @BookSummary,@UserID, @BookComment,@Now)RETURN GO
View 4 Replies
View Related
Jun 4, 2008
hi, i have a sqlserver2005 table 'member' with the fields 'id','name','datecreated','datemodified' and 'memberlevel' (which the default value is 1).
Now i want to create a stored procedure that will run automatically once a day,
this procedure will reset to 0 the 'memberlevel' that are 1 if the 'datemodified' is greater than 3 months.
can anyone help me on this, thanks!
View 2 Replies
View Related
Jun 12, 2008
When I verify syntax, I get this error:
Msg 1038, Level 15, State 4, Procedure webservices_BENEFICIAL_USES_DM_SELECT, Line 8
An object or column name is missing or empty. For SELECT INTO statements, verify each column has a name. For other statements, look for empty alias names. Aliases defined as "" or [] are not allowed. Add a name or single space as the alias name.
Here's the proc:
CREATE PROCEDURE webservices_BENEFICIAL_USES_DM_SELECT
-- Add the parameters for the stored procedure here
@DISPOSAL_AREA_NAME varchar(40) = ""
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
SELECT*
FROMBENEFICIAL_USES_DM
END
GO
Thanks.
View 6 Replies
View Related
Mar 31, 2007
Hey all,
Can anyone tell me why this stored procedure doesn't work?
ALTER PROCEDURE [dbo].[RemoveLocation]
@Id int,
@Name varchar
AS
DELETE FROM Classifieds_Terminals
WHERE [Id] = @Id and [Name] = @Name
I try exeuting it like this:
USE [CLASSIFIEDSDB2.MDF]
GO
DECLARE @return_value int
EXEC @return_value = [dbo].[RemoveLocation]
@Id = 18,
@Name = N'Terminal A'
SELECT 'Return Value' = @return_value
GO
It returns 0 and nothing happens....???
Here is the table:
id | Name
18 Terminal A18 Terminal B18 Terminal C
View 2 Replies
View Related
Apr 2, 2007
Hi,
I got a problem of a sp. Did the sp can use over one GO script? It is because after I create the below scripts in the sp and checked no syntax error and then apply changed. I re-open this sp, it just save my scripts upto the first GO statement . The scripts from "IF EXISTS..." has not saved. But I try to run the script in analyzer is successful. How can I fix it in Enterprise manager?
Thanks.
Here is my test code:
CREATE PROCEDURE sp_Test AS--set nocount onSELECT id, name from MainGO--insert the record in SimpleReports tableIF EXISTS (select ID from Main where id=5)delete from Main where id=5GOINSERT INTO Main ( ID, Name, Quantity)VALUES ( '6', 'hij', '60')GO
View 4 Replies
View Related
Jan 16, 2008
I have what I hope is a simple question about a stored procedure. If this is not the right spot for this, I offer my apologies.
I am planning on calling a stored procedure from my ASP.Net site using C#. What I want to do is pass a variable to the procedure and using that varible, put together another variable that I will pass back.
I am very new to all but the most basic stored procedures so I was not shocked when my code didnt work. Can anyone toss me a hint? I am actively working on this and if I find anything on a website to assist and have a breakthru, I will be sure to update you all.
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
goCREATE PROCEDURE [dbo].[GetAttySectyData] (@ID as int) AS
declare @First as varchar(200)
declare @Middle as varchar(50)
declare @Last as varchar(200)
declare @Name as varchar(255)@First = SELECT value FROM dbo.text WHERE efield_id = 10741 AND employee_id = @ID
@Middle = SELECT value FROM dbo.text WHERE efield_id = 10906 AND employee_id = @ID
@Last = SELECT value FROM dbo.text WHERE efield_id =10740 AND employee_id = @ID
Select @First+' '+@Middle+' '+@Last from text AS @Name
RETURN
View 3 Replies
View Related
May 21, 2008
I am using SQL Server Express within visual studio and am needing to create a stored procedure. The proble is that every time I finish and close it then it asks me if I want to save the changes which of course I want to.
But then it always comes up with a message box that says "invalid object name" followed with the name of the stored procedure.
??? why? I try creating a stored procedure from scratch or copying the code of another stored procedure. It alway gives me that message and does not let me save the stored procedure. I tried changing the default name of "dbo.storedprocedure1" or even leaving it as is and always I get the message that says "invalid object name: dbo.storedprocedure1" and it does not let me save the procedure.
What am I doing wrong?
View 5 Replies
View Related
Jan 28, 2005
I am trying to figure out the syntax for an SQL statement I have in a stored procedure.CREATE PROCEDURE dbo.forum_post_add
@forumId integer,
@fpTopic char(100),
@fpBody text,
@fpPoster integer
AS
Insert into forumPost (forumId, fpTopic, fpBody, fpPoster) values (@forumId, @fpTopic, @fpBody, @fpPoster)
UPDATE forum SET forumThreads = forumThreads + 1 where forumId = @forumId
UPDATE forumGroup SET fgThreads = fgThreads + 1 where fgID = select forumGroup from forum where forumId = @forumId
GOThe statement in question is the second UPDATE. How do I create an UPDATE statement that selects that correct record based on the single result of a SELECT statement?
I am also not too sure about the 'fgThreads = fgThreads + 1' thing, is this the best way to increment a value?
I know I should also be using transactions and probably a few other bells and whistles but I'll worry about that later.
Thanks
Martin
View 4 Replies
View Related
Jul 14, 2005
I have a SP below that authenticates users, the problem I have is that activate is of type BIT and I can set it to 1 or 0.
If I set it to 0 which is disabled, the user can still login.
Therefore I want users that have activate as 1 to be able to login and users with activate as 0 not to login
what are mine doing wrong ?
Please help
CREATE PROCEDURE DBAuthenticate
(
@username Varchar( 100 ),
@password Varchar( 100 )
)
As
DECLARE @ID INT
DECLARE @actualPassword Varchar( 100 )
SELECT
@ID = IdentityCol,
@actualPassword = password
FROM CandidatesAccount
WHERE username = @username and Activate = 1
IF @ID IS NOT NULL
IF @password = @actualPassword
RETURN @ID
ELSE
RETURN - 2
ELSE
RETURN - 1
GO
View 5 Replies
View Related
Feb 13, 2007
If I put 5 or 6 stored procedures within another stored procedure will they be executed sequentially or all at once? Is there any method. I can manage this process with some code I was just wondering how query analyzer or an sp would handle this by default.
create dbo.storedprocedure as update_data
exec sp_1
exec sp_2
exec sp_3
etc.
View 12 Replies
View Related
Dec 4, 2006
Hi All,I am pretty new to stored procedure and I have a quick question. Iappreciate any help offered.I am writing a stored procedure as follow. This is just an example:select field1, field2 from table1 where field1 = 1I need to change this stored procedure so that if field2=0 then it willbe displayed on the web application as 'Yes', if field2=1 then I needto display the text 'No' on the web application.Field2's type is bit, ie: its' value is either 1 or 0.Thanks for any suggestion.Sincerely,Teresa
View 2 Replies
View Related
Mar 24, 2008
I'm executing simple stored procedure same to:
create procedure [dbo].[usp_procedure]@var1 int=null output,@var2 varchar(25)=null output,@var3 varchar(25)=null output,@var4 varchar(25)=null output,@var5 varchar(25)=null output,@action char(1)=null outputasbegin set nocoutn on;if @action = 'S'begin select * from t_table order by @var1end
in my script I call to this stored procedure but, this send me a error. my script is
$action="S";// Set up T-SQL query.$tsql = "call dbo.usp_T_Usuarios (@action=?)";// Assign parameter values.$params = array($action);// Specify types for parameters.$stmt = sqlsrv_query( $conn, $tsql, $params);echo "<br><br>Executed.....<br><br>";// Create and execute the statement. Display any errors that occur.if(!$stmt){ echo "Error executing the sp...<br>"; die( print_r( sqlsrv_errors(), true));}else{ echo "<br>Execute sp....<br><br>"; echo $stmt;}
and the error is:
conected...The conection was succefully.Executed.....Error executing the sp...Array ( [0] => Array ( [0] => 07009 [SQLSTATE] => 07009 [1] => 0 [code] => 0 [2] => [Microsoft][SQL Native Client]Ă?ndice descriptor no vĂ¡lido [message] => [Microsoft][SQL Native Client]Ă?ndice descriptor no vĂ¡lido ) [1] => Array ( [0] => 07009 [SQLSTATE] => 07009 [1] => 0 [code] => 0 [2] => [Microsoft][SQL Native Client]NĂºmero de parĂ¡metros no vĂ¡lido [message] => [Microsoft][SQL Native Client]NĂºmero de parĂ¡metros no vĂ¡lido ) )I need help me. some body tell me what is my error. please.
View 9 Replies
View Related
May 19, 2007
I am developing a windows application in VB.Net 2005 and Database is SQl Server 2000.I want to insert, update and delete records from a master table which has 8 columns.So should I write a stored procedure for this or write three queries and execute them in code.I haven't used stored procedure before. What will be advantages of using stored procedures?And tell me how to write stored procedure to insert,update and delete. Then how to call it in VB.Net code.
View 7 Replies
View Related
Apr 9, 2008
I suspect I have a but in a stored procedure that is used to delete records in a table. Here's the code for the gridview and its SQLDataSource
1 <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False"
2 DataKeyNames="table2_id" DataSourceID="SqlDataSource1">
3 <Columns>
4 <asp:CommandField ShowDeleteButton="True" ShowSelectButton="True" />
5 <asp:BoundField DataField="table2_id" HeaderText="table2_id"
6 InsertVisible="False" ReadOnly="True" SortExpression="table2_id" />
7 <asp:BoundField DataField="table1_id" HeaderText="table1_id"
8 SortExpression="table1_id" />
9 <asp:BoundField DataField="name_2" HeaderText="name_2"
10 SortExpression="name_2" />
11 <asp:CheckBoxField DataField="yesno" HeaderText="yesno"
12 SortExpression="yesno" />
13 <asp:BoundField DataField="MyArtificialColumn" HeaderText="MyArtificialColumn"
14 ReadOnly="True" SortExpression="MyArtificialColumn" />
15 </Columns>
16 </asp:GridView>
17 <asp:SqlDataSource ID="SqlDataSource1" runat="server"
18 ConnectionString="<%$ ConnectionStrings:DatabaseConnectionString %>"
19 DeleteCommand="EraseIt" DeleteCommandType="StoredProcedure"
20 SelectCommand="StoredProcedure5" SelectCommandType="StoredProcedure">
21 </asp:SqlDataSource>
Recently, "Wreck of u" kindly provided me with the stored procedure "StoredProcedure5" (line 20) which populates the gridview as shown. What I would like to now is write a stored procedure that DELETES a row. As like 19 shows, I attempted to write such an SP called EraseIt. However, when I click on Delete in the browser for a row, I get the error message "Procedure EraseIt has no parameters and arguments were supplied. The following is the code for EraseIt. Do I in fact have an error with specifying the input parameter "@table2_id", and or am I doing something else wrong? 1 ALTER PROCEDURE [dbo].[EraseIt]
2 AS
3 DECLARE @TABLE2_ID int
4 BEGIN
5 DELETE FROM DBO.TABLE2 WHERE TABLE2_ID = @TABLE2_ID
6 END
7
Thanks!-Kurt
View 5 Replies
View Related
Oct 29, 2014
I got some xml that is essentially an html table that I need to turn into a standard table resultset from a stored proc. If you take this xml and save it as html that is the desired resultset I am looking for. I realize the <td> tags repeat so I would just prefer 'col' + positional index for the col name. Keep in mind that <td> could be is 1 to n.
<table>
<tr>
<td>cell1</td>
<td>cell2</td>
<td>cell3</td>
[Code] .....
This is my attempt but I can't figure out how to get separate cols
declare @GridData xml = '<table><tr><td>cell1</td><td>cell2</td><td>cell3</td></tr><tr><td>cell4</td><td>cell5</td><td>cell6</td></tr><tr><td>cell7</td><td>cell8</td><td>cell8</td></tr></table>'
select T.C.value('.', 'nvarchar(max)')
from @GridData.nodes('//tr') T(C)
View 6 Replies
View Related
Oct 16, 2007
Hi ,
I ma using sql server 2005.I have a bunch of statements of sql and i have created a stored procedure for those. When i execute i found that there is lot's of difference between execution time of stored procedure and direct sql in query windows.
can anyone help me to optimize the execution time for stored prcedure even stored prcedure is very simple.
I have used sql server 2000 and i am new in sql server 2005.
View 1 Replies
View Related
Jun 4, 2002
l would like to intergrate the the two procedures listed below into one procedure or function.
l'm doing it in two steps.This report will have each loan listed and a final line summed on each of the amounts?
enddate and startdate being my parameters and print the results to screen.And l'm failing to do that thats why its in two steps.Hope this makes it clear enough.
-- Exec New '2001-04-01 00:00:00.000','2002-05-29 23:59:59.000'
-- Exec New1 '2001-04-01 00:00:00.000','2002-05-29 23:59:59.000'
Alter procedure new (@startdate datetime,@enddate datetime)
as
begin
SELECT Distinct
Loan_no AS Loan_no,
Date_Issued AS Date_Issued,
Store AS Store,
Product AS Product,
Capital_Amount AS Capital_Amount,
Interest_Amount AS Interest_Amount,
Total_Amount AS Total_Amount,
Insurance_amount As Insurance_Amount,
Admin_Fee AS Admin_Fee,
User_Issued AS User_Issued,
LoanBook AS Company,
Status
FROM Loan
Where Date_Issued BETWEEN '2001-04-01 00:00:00.000' And '2002-05-29 23:59:59.000'
End
Alter procedure new1(@startdate datetime,@enddate datetime)
As
Begin
SELECT
Sum(Capital_Amount) AS Capital_Amount,
Sum(Interest_Amount) AS Interest_Amount,
Sum(Total_Amount) AS Total_Amount,
Sum(Insurance_amount) As Insurance_Amount,
Sum(Admin_Fee) AS Admin_Fee
FROM Loan
Where Date_Issued BETWEEN '2001-04-01 00:00:00.000' And '2002-05-29 23:59:59.000'
End
View 1 Replies
View Related
Jul 23, 2005
I have a department table like this:DeptID Department ParentID, Lineage1 HR NULL (2 Temp1 1 (1,3 Temp2 2 (1,24 PC NULL (I have a deptmember table like this:DeptID MemberID IsManager1 1 Y4 1 YI need to query table to get all department belong to MemberID 1 withall children departments.My thought is:1. Do Select * from deptmember where MemberID=1 and IsManager=Y2. Loop thru this table to build SQLWhere Lineage like '%1' OR Lineage like '%4'3. Select * from department using where statement from step 2.How do you loop thru results from step1, Do I need to use a cursor?Thanks,HL
View 3 Replies
View Related
Mar 22, 2006
Hello,
we have automated build on every night. In our solution is SSIS project, where each package is encrypted per password. We call build process per command line like this..
C:ProgrammeMicrosoft Visual Studio 8Common7IDEdevenv.exe (c:DevelopmentX3\X3.sln /build Release)' in 'c:DevelopmentProjectsDailyBuild
Through build process we get a error:
External Program Failed: C:ProgrammeMicrosoft Visual Studio 8Common7IDEdevenv.exe (return code was 1):
We think a reason is, that on build of SSIS project must be entered a password. You can wonder for what we need that SSIS packages are part of our build. We hope that on build process is also created Deployment Utility, if so set in dtproject.user. Is it so? Is there any way to create Deployment utility on automated build process? Can be a password provided pre command line?
with best regards
Anton Kalcik
View 5 Replies
View Related
May 13, 2015
create all the maintenance scripts (SProcs) to my maintenance databases in an automated way?I have around 12 procedures which I don't want to consolidate in a single Proc and execute.Another problem which is coming to my mind is, I can';t use the below method to execute the proc, as I dont have RDP access to the servers.
sqlcmd -i C:MyFolderMyScript.sql -o C:MyFolderMyOutput.txt
I am thinking to ask for a central location where I can create a folder , dumping all my scripts and build the maintenance DB and creating all my procs, table, etc.
View 1 Replies
View Related
Feb 28, 2007
Hi,
I plan to use my stored procedures to build the ad-hoc reporting model
Can someone please tell me where to use the OPENROWSET hack mentioned in this post (below) to create a datasource view for building the Report Model.
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=345209&SiteID=1
Appreciate your help
Thanks.
View 1 Replies
View Related
Jul 20, 2005
HiI'm trying to make this to work and need helpHere my SP and I'm building sql with output param.Alter PROCEDURE lpsadmin_getSBWReorderDollars(@out decimal(10,2) output,@sType varchar(20),@dSearchDateFrom datetime,@dSearchDateTo datetime,@sOrderType char(1))ASDECLARE @sql as nvarchar(4000)SELECT @sql = 'SELECT @out = SUM(Price*Quantity)FROM PortraitReOrderOrder jcpreINNER JOIN Orders jcporON OrderID = OrderIDWHERE jcpor.Archive = 0AND jcpre.CreatedDate between ''' + CONVERT(varchar(10),@dSearchDateFrom, 101) + ''' AND ''' + CONVERT(varchar(10),@dSearchDateTo, 101) + ''''IF @sOrderType <> 0SELECT @sql = @sql + ' AND LEFT(OrderType,3) = (SELECT OrderTypeNameFROM OrderTypes WHERE OrderTypeID = ' + @sOrderType + ')'IF @sType = 'Active'SELECT @sql = @sql + ' AND PATINDEX(''%SHR%'', AccessCode) = 0 'IF @sType = 'Shared'SELECT @sql = @sql + ' AND PATINDEX(''%SHR%'', AccessCode) <> 0 'Print @sqlEXECUTE sp_executesql @sqlIt gives me an error messageMust declare the variable '@out'.Please help
View 2 Replies
View Related
Nov 23, 2006
/*Subject: How to build a procedure that returns differentnumbers of columns as a result based on a parameter.You can copy/paste this whole post in SQL Query Analyzeror Management Studio and run it once you've made surethere is no harmful code.Currently we have several stored procedures which finalresult is a select with several joins that returns manycolumns (150 in one case, maybe around 50 the average).We have analyzed our application and found out that mostof the time not all the columns are used. We haveidentified 3 different sets of columns needed indifferent parts of the application.Let's identify and name these sets as:1- simple set, return the employee list for example2- common set, return the employee information (whichinclude the simple set)3- extended set, return the employee information (whichinlude the common set which itself includes the simpleset) + additional information from other tables, maybeeven some SUM aggregates and so on (I don't know forexample, how much sales the employee did so far).So the bigger sets contain the smaller ones. Please keepreading all the way to the bottom to better understandtechnically what we are trying.Here is a code sample of how our current procedureswork. Please note that the passing parameter we can eitherpass a Unique Identifier (PK) to retrieve a single record,or if we pass for example -1 or NULL we retrieve all theemployee records.*/create table a ( apk int primary key, af1 int, af2 int, af3 int, af4int, af5 int, af6 int)create table b ( bpk int primary key, bf1 int, bf2 int, bf3 int, bf4int, bf5 int, bf6 int)create table c ( cpk int primary key, cf1 int, cf2 int, cf3 int, cf4int, cf5 int, cf6 int)create table d ( dpk int primary key, df1 int, df2 int, df3 int, df4int, df5 int, df6 int)insert a values (1,1111,1112,1113,1114,1115,1116)insert a values (2,1211,1212,1213,1214,1215,1216)insert a values (3,1311,1312,1313,1314,1315,1316)insert a values (4,1411,1412,1413,1431,1415,1416)insert a values (5,1511,1512,1513,1514,1515,1516)insert a values (6,1611,1612,1613,1614,1615,1616)insert b values (1,2111,2112,2113,2114,2115,2116)insert b values (2,2211,2212,2213,2214,2215,2216)insert b values (3,2311,2312,2313,2314,2315,2316)insert b values (4,2411,2412,2413,2431,2415,2416)insert b values (5,2511,2512,2513,2514,2515,2516)insert b values (6,2611,2612,2613,2614,2615,2616)insert c values (1,3111,3112,3113,3114,3115,3116)insert c values (2,3211,3212,3213,3214,3215,3216)insert c values (3,3311,3312,3313,3314,3315,3316)insert c values (4,3411,3412,3413,3431,3415,3416)insert c values (5,3511,3512,3513,3514,3515,3516)insert c values (6,3611,3612,3613,3614,3615,3616)insert d values (1,4111,4112,4113,4114,4115,4116)insert d values (2,4211,4212,4213,4214,4215,4216)insert d values (3,4311,4312,4313,4314,4315,4316)insert d values (4,4411,4412,4413,4431,4415,4416)insert d values (5,4511,4512,4513,4514,4515,4516)insert d values (6,4611,4612,4613,4614,4615,4616)gocreate procedure original_proc @pk int asif @pk = -1set @pk = nullselecta.af1, a.af2, a.af3, a.af4, b.bf1, b.bf2, b.bf3, b.bf4, c.cf1, c.cf2,c.cf3, c.cf4, d.df1, d.df2, d.df3, d.df4fromajoin b on a.apk = b.bpkjoin c on b.bpk = c.cpkjoin d on c.cpk = d.dpkwherea.apk = ISNULL(@pk, a.apk)goexec original_proc 1go/*Currently the above SP is a single SP that is basicallyreturning ALL possible needed data. However most of thetime we might need to call and retrieve a simple employeelist.So we thought about modifying the stored procedure byadding an extra parameter that will indicate which setof columns to return.For modifying the stored procedure in order to get avariable name of columns returned and avoidingrepeating code, we built 4 objects: the storedprocedure being called, one table function and 2 views.One table function so that we are able to pass a parameter.The views since they do not accept parameters they arealways joined at least with the inline table function.The stored procedure generates in its body a dynamicSQL statement, where it queries the table function andthe views, depending which set is required. Here is acode sample of our current design (you need to run theprevious code in order for this to work).*/create function _1_set(@pk int)returns tableas return(select a.apk, a.af1, a.af2, a.af3, a.af4, b.bf1, b.bf2from ajoin b on a.apk = b.bpkwhere a.apk = ISNULL(@pk, a.apk))gocreate view _2_set asselect b.bpk, b.bf3, b.bf4, c.cf1, c.cf2from bjoin c on b.bpk = c.cpkgocreate view _3_set asselect c.cpk, c.cf3, c.cf4, d.df1, d.df2, d.df3, d.df4from cjoin d on c.cpk = d.dpkgocreate procedure new_proc @pk int, @set int asdeclare @sql nvarchar(4000)if @pk = -1set @pk = nullset @sql = 'select * from _1_set(@pk) fs 'if @set 1set @sql = @sql + 'join _2_set ss on fs.apk = ss.bpk 'if @set 2set @sql = @sql + 'join _3_set ts on ss.bpk = ts.cpk 'exec sp_executesql @sql, N'@pk int', @pkgoexec new_proc 1, 3go/*For executing the new procedure, we pass parameter 1for the smaller set, 2 for the medium size set or 3for the complete set.For example when we want to retrieve the common setwe pass the Unique Identifier of the employee to theSP and then we pass the type of set we want to useas the second parameter (1 for simple set, 2 forcommon set and 3 for extended set).The SP has the IF and dynamic SQL to add more JOINs.We would like to know what you think of this approachand if you know a simpler way of doing it.For cleaning up the test objects run the following code.*/drop procedure original_procdrop procedure new_procdrop function _1_setdrop view _2_setdrop view _3_setdrop table adrop table bdrop table cdrop table dAs always I would appreciate any feedback, opinion,comments, ideas and suggestions.Thank you
View 9 Replies
View Related
Nov 27, 2007
Hi all,
I have access to a stored procedure that was written previously for a process that uses the output from the stored procedure to provide input to a BCP operation in a bat file that builds a flat text file for use in a different system.
To continue with the set up, here is the stored procedure in question:
CREATE PROCEDURE [dbo].[HE_GetStks] AS
select top 15
Rating,
rank,
coname,
PriceClose,
pricechg,
DailyVol,
symbol
from
(selectf.rating,
f.rank,
s.coname,
cast ( f.priceclose as decimal(10,2)) as PriceClose,
cast ( f.pricechg as decimal(10,2)) as pricechg,
f.DailyVol,
f.symbol
from dailydata f, snames s
where f.tendcash = 0
and f.status = 1
and f.typ = 1
and f.osid = s.osid) tt
order by rating desc, rank desc
GO
The code in the calling bat file is:
REM *************************
REM BCP .WRK FILE
REM *************************
bcp "exec dailydb.[dbo].[HE_GetStks]" queryout "d:TABLESINPUTHE_GetStks.WRK" -S(local) -c -U<uname> -P<upass>
This works just peachy in the process for which it was designed, but I need to use the same stored procedure to grab the same data in order to store it in a historical table in the database. I know I could duplicate the code in a separate stored procedure that does the inserting into my database table, but I would like to avoid that and use this stored procedure in case the select statement is changed at some point in the future.
Am I missing something obvious in how to utilize this stored procedure from inside an insert statement in order to use the data it outputs? I know I cannot use an EXECUTE HE_GetStks as a subquery in my insert statement, but that is, in essence, what I am trying to accomplish.
I just wanted to bounce the issue of y'all before I go to The Boss and ask him to change the procedure to SET the data into a database table directly (change the select in the proc to an INSERT to a local table) then have the external BAT file use a GET procedure that just does the select from the local table. This is the method most of our similar jobs use when faced with this type of "intercept" task.
Any thoughts?
View 6 Replies
View Related
Nov 1, 2007
Hi all - I'm trying to optimized my stored procedures to be a bit easier to maintain, and am sure this is possible, not am very unclear on the syntax to doing this correctly. For example, I have a simple stored procedure that takes a string as a parameter, and returns its resolved index that corresponds to a record in my database. ie
exec dbo.DeriveStatusID 'Created'
returns an int value as 1
(performed by "SELECT statusID FROM statusList WHERE statusName= 'Created')
but I also have a second stored procedure that needs to make reference to this procedure first, in order to resolve an id - ie:
exec dbo.AddProduct_Insert 'widget1'
which currently performs:SET @statusID = (SELECT statusID FROM statusList WHERE statusName='Created')INSERT INTO Products (productname, statusID) VALUES (''widget1', @statusID)
I want to simply the insert to perform (in one sproc):
SET @statusID = EXEC deriveStatusID ('Created')INSERT INTO Products (productname, statusID) VALUES (''widget1', @statusID)
This works fine if I call this stored procedure in code first, then pass it to the second stored procedure, but NOT if it is reference in the second stored procedure directly (I end up with an empty value for @statusID in this example).
My actual "Insert" stored procedures are far more complicated, but I am working towards lightening the business logic in my application ( it shouldn't have to pre-vet the data prior to executing a valid insert).
Hopefully this makes some sense - it doesn't seem right to me that this is impossible, and am fairly sure I'm just missing some simple syntax - can anyone assist?
View 1 Replies
View Related
Mar 3, 2008
Hi all,
I have 2 sets of sql code in my SQL Server Management Stidio Express (SSMSE):
(1) /////--spTopSixAnalytes.sql--///
USE ssmsExpressDB
GO
CREATE Procedure [dbo].[spTopSixAnalytes]
AS
SET ROWCOUNT 6
SELECT Labtests.Result AS TopSixAnalytes, LabTests.Unit, LabTests.AnalyteName
FROM LabTests
ORDER BY LabTests.Result DESC
GO
(2) /////--spTopSixAnalytesEXEC.sql--//////////////
USE ssmsExpressDB
GO
EXEC spTopSixAnalytes
GO
I executed them and got the following results in SSMSE:
TopSixAnalytes Unit AnalyteName
1 222.10 ug/Kg Acetone
2 220.30 ug/Kg Acetone
3 211.90 ug/Kg Acetone
4 140.30 ug/L Acetone
5 120.70 ug/L Acetone
6 90.70 ug/L Acetone
/////////////////////////////////////////////////////////////////////////////////////////////
Now, I try to use this Stored Procedure in my ADO.NET-VB 2005 Express programming:
//////////////////--spTopSixAnalytes.vb--///////////
Public Class Form1
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim sqlConnection As SqlConnection = New SqlConnection("Data Source = .SQLEXPRESS; Integrated Security = SSPI; Initial Catalog = ssmsExpressDB;")
Dim sqlDataAdapter As SqlDataAdapter = New SqlDataAdaptor("[spTopSixAnalytes]", sqlConnection)
sqlDataAdapter.SelectCommand.Command.Type = CommandType.StoredProcedure
'Pass the name of the DataSet through the overloaded contructor
'of the DataSet class.
Dim dataSet As DataSet ("ssmsExpressDB")
sqlConnection.Open()
sqlDataAdapter.Fill(DataSet)
sqlConnection.Close()
End Sub
End Class
///////////////////////////////////////////////////////////////////////////////////////////
I executed the above code and I got the following 4 errors:
Error #1: Type 'SqlConnection' is not defined (in Form1.vb)
Error #2: Type 'SqlDataAdapter' is not defined (in Form1.vb)
Error #3: Array bounds cannot appear in type specifiers (in Form1.vb)
Error #4: 'DataSet' is not a type and cannot be used as an expression (in Form1)
Please help and advise.
Thanks in advance,
Scott Chang
More Information for you to know:
I have the "ssmsExpressDB" database in the Database Expolorer of VB 2005 Express. But I do not know how to get the SqlConnection and the SqlDataAdapter into the Form1. I do not know how to get the Fill Method implemented properly.
I try to learn "Working with SELECT Statement in a Stored Procedure" for printing the 6 rows that are selected - they are not parameterized.
View 11 Replies
View Related
