Stored Procedure - SELECT INSERT Statement - Good Practice?
Jan 9, 2004
I would like to have a stored procedure executed once a week via a DTS package. The data I would like inserted into Table_2, which is the table where the DTS is being executed on, comes from a weekly dump from Oracle into a Table_1 via another DTS package.
I would like to only import data since the last import so I was thinking of my logic to be like this:
INSERT INTO Table_2
(Field1, Field2, ... , FieldN)
VALUES (SELECT Field1, Field2, ... , FieldN FROM Table_1 WHERE ThisDate > MAX(Table_2.ThatDate))
Does this make sense? Or do you all suggest a different mannger of accomplishing this?
FoodID Integer PRIMARY KEY, FoodName varchar(255), FoodDesc text
How do I insert into this table while checking that the FoodName do not replicate? I'm aware that with a stored procedure I'm able to you the IF EXIST statement to help me solve this problem. But if I do not wish to use the stored procedure, am I able to create a SQL string to insert while checking the condition?
Thanks in advance to the people that replied to my request! Thanks so much...
I have several places where I need to get the id (primary key) of a resource, inserting a row if the resource does not exist (i.e. an artificial key to be used as an FK for another table). I should probably change this varchar key lookup to use a hash index, but that is beside the point.
So the table is essentially like: CREATE TABLE MyLookup(id int identity primary key nonclustered, mykey varchar(256)); CREATE CLUSTERED INDEX mylookup_cidx_mykey ON MyLookup(mykey);
I see two main approaches for how I can do my get-id-with-insert-if-needed.
(Approach 1) DECLARE @id INT; SELECT @id = id FROM MyLookup WHERE mykey = 'some key value'; IF (@id is null) BEGIN
INSERT MyLookup ('some key value'); SET @id = SCOPE_IDENTITY(); END
(Approach 2) DECLARE @id INT; INSERT MyLookup SELECT 'some key value' WHERE NOT EXISTS (SELECT id FROM MyLookup WHERE mykey = 'some key value'); IF (@@ROWCOUNT = 0)
SELECT @id = id FROM MyLookup WHERE mykey = 'some key value'; ELSE
SET @id = SCOPE_IDENTITY();
From some quick tests in profiler, approach 2 seems to be a bit faster and have lower resource utilization. But I'm not sure if it maybe takes some more aggressive locks even in the unnecessary case where the mykey row value of 'some key value' already exists. Approach 2 also looks cleaner to me, but I don't mind a bit of extra code if it gives me better scalability through less lock contention.
Any tip on what is considered the best practice for a conditional insert like this, or a tip on how to get detailed lock info for a query? The lock info for profiler was all greek to me, it just had a hex value with each lock acquired/released, so I have no idea what it was telling me. Is my only solution to just run exhaustive tests and look at the perf numbers from the black box?
I have written a stored procedure which has in it a select statement. This returns a single record (I used the "top 1" line to ensure I only get a single record). I want to use a value returned by this statement (not the primary key) further on in the stored procedure - so assigning it to a variable would be handy.
Hi there, I am trying to return data from a select statement that is running a stored procedure.
I am running on SQL 2000 SP4. I have created a 'loopback' linked server as described in a technet article. (It's pointing at itself) I can then use the following select statement to get data back in the select statement from a stored procedure.
select * from openquery(loopback,'exec DWStaging.dbo.PokerStaging')
I am trying to get data back from my own Stored Procedure, but continue to get the following error:
Server: Msg 7357, Level 16, State 1, Line 1 Could not process object 'exec DWStaging.dbo.PokerStaging'. The OLE DB provider 'SQLOLEDB' indicates that the object has no columns. OLE DB error trace [Non-interface error: OLE DB provider unable to process the object:ProviderName='SQLOLEDB', Query=exec DWStaging.dbo.PokerStaging'].
If I try the same syntax with the sp_who stored procedure, it works and I get data back fine. But, If I try it with the sp_who2 stored procedure I get the same error as above.
select * from openquery(loopback,'exec sp_who') --> Works fine select * from openquery(loopback,'exec sp_who2') --> Doesn't work
Does anyone know what the difference is between the Stored Procedures that work with this syntax and those that don't? Is there a way I can change my stored procedure such that it would cause it to work?
PS: The following code was used to create the linked server, and then a security pass though as a certain account was addedDECLARE @provstr varchar (2000) SET @provstr = 'PROVIDER=SQLOLEDB;SERVER=' + @@SERVERNAME EXEC sp_addlinkedserver 'loopback', @srvproduct = 'MSSQL', @provider = 'SQLOLEDB', @provstr = @provstr
Hello,I have written a stored procedure where I prompt the user for the Year and the Month (see below)How do I take the variable @TheMonth and find out how many days is in the month and then loop to display a total for every day in the selected month.Can someone please point me in the right direction. CREATE PROCEDURE crm_contact_frequency_report @TheYear varchar(4),@TheMonth varchar(2) AS SELECT /* EMAILS (B) */(SELECT COUNT(*) FROM dbo.CampaignResponse A INNER JOIN Email B ON A.subject = B.subject WHERE (YEAR(B.CreatedOn) = @TheYear) AND (MONTH(B.CreatedOn) = @TheMonth) AND (B.directioncode = 1)) AS Total_EmailOutgoing, (SELECT COUNT(*) FROM dbo.CampaignResponse A INNER JOIN Email B ON A.subject = B.subject WHERE (YEAR(B.CreatedOn) = @TheYear) AND (MONTH(B.CreatedOn) = @TheMonth) AND (B.directioncode = 0)) AS Total_EmailImconing, (SELECT COUNT(*) FROM dbo.CampaignResponse A INNER JOIN Email B ON A.subject = B.subject WHERE (YEAR(B.CreatedOn) = @TheYear) AND (MONTH(B.CreatedOn) = @TheMonth) AND (B.directioncode IS NULL)) AS Total_EmailNotListed, (SELECT COUNT(*) FROM dbo.CampaignResponse A INNER JOIN Email B ON A.subject = B.subject WHERE (YEAR(B.CreatedOn) = @TheYear) AND (MONTH(B.CreatedOn) = @TheMonth)) AS Total_All_Emails, /* PHONE CALLS (C) */(SELECT COUNT(*) FROM dbo.CampaignResponse A INNER JOIN PhoneCall C ON A.subject = C.subject WHERE (YEAR(C.CreatedOn) = @TheYear) AND (MONTH(C.CreatedOn) = @TheMonth) AND (C.directioncode = 1)) AS Total_CallOutgoing, (SELECT COUNT(*) FROM dbo.CampaignResponse A INNER JOIN PhoneCall C ON A.subject = C.subject WHERE (YEAR(C.CreatedOn) = @TheYear) AND (MONTH(C.CreatedOn) = @TheMonth) AND (C.directioncode = 0)) AS Total_CallIncoming, (SELECT COUNT(*) FROM dbo.CampaignResponse A INNER JOIN PhoneCall C ON A.subject = C.subject WHERE (YEAR(C.CreatedOn) = @TheYear) AND (MONTH(C.CreatedOn) = @TheMonth) AND (C.directioncode IS NULL)) AS Total_CallNotListed, (SELECT COUNT(*) FROM dbo.CampaignResponse A INNER JOIN PhoneCall C ON A.subject = C.subject WHERE (YEAR(C.CreatedOn) = @TheYear) AND (MONTH(C.CreatedOn) = @TheMonth)) AS Total_All_Calls, /* FAXES (D) */(SELECT COUNT(*) FROM dbo.CampaignResponse A INNER JOIN Fax D ON A.subject = D.subject WHERE (YEAR(D.CreatedOn) = @TheYear) AND (MONTH(D.CreatedOn) = @TheMonth) AND (D.directioncode = 1)) AS Total_FaxOutgoing, (SELECT COUNT(*) FROM dbo.CampaignResponse A INNER JOIN Fax D ON A.subject = D.subject WHERE (YEAR(D.CreatedOn) = @TheYear) AND (MONTH(D.CreatedOn) = @TheMonth) AND (D.directioncode = 0)) AS Total_FaxIncoming, (SELECT COUNT(*) FROM dbo.CampaignResponse A INNER JOIN Fax D ON A.subject = D.subject WHERE (YEAR(D.CreatedOn) = @TheYear) AND (MONTH(D.CreatedOn) = @TheMonth) AND (D.directioncode IS NULL)) AS Total_FaxNotListed, (SELECT COUNT(*) FROM dbo.CampaignResponse A INNER JOIN Fax D ON A.subject = D.subject WHERE (YEAR(D.CreatedOn) = @TheYear) AND (MONTH(D.CreatedOn) = @TheMonth)) AS Total_All_Faxes FROM CampaignResponse AGO
Is there a way to call a stored procedure within a SELECT statement?Example;-----------SELECT FirstName, LastName, (EXEC UniqueID_KEYGEN @keyval output) AS UniqueIDINTO #tNewEmployeeFROM EmployeeTable-----------SELECT *FROM #tNewEmployeeThe return from the temp table would have a unique ID ready to insert into another table. Our DBA has this stored procedure to create unique ID's and is to be used on all INSERTS. I was used to having a Identity field do this for me, I don't know why we have to do it his way. Except for the reason of sequence and easily get the next record. But we don't use URL variables, only FORM or SESSION.Thanks for your help in advance.
I have a stored procedure in an Informix-database that returns a string. Its used in a SELECT statement like this. SELECT t1.id, t2.id, sp_name(t1.id, t2.id) FROM table1 t1, table2 t2
I want to write it in SQLserver. I have tried this syntax but get error. Server: Msg 170, Level 15, State 1, Line 1 Line 1: Incorrect syntax near 't'.
Can a stored procedure be executed from within a select statement?
Given a store procedure named: sp_proc
I wish to do something like this:
For each row in the table execute sp_proc 'parameter1', parameter2'... end for ...but within a select statement. I know you can do this with stored functions, just not sure what the syntax is for a stored procedure.
Can this be done? I want to call a stored procedure from inside a select statement. Since you can nest select statements, I thought it might be possible but I have no idea how to do it.
USE NORTHWIND GO
CREATE TABLE tbA ( Item int NOT NULL, Value int NOT NULL ) ON [PRIMARY]
GO
INSERT INTO tbA (Item, Value) SELECT 1, 10 UNION ALL SELECT 2, 5 UNION ALL SELECT 3, 2 GO
CREATE PROCEDURE usp_SquareIt
@iItem int
AS
declare @iValue int SELECT @iValue = Value FROM tbA SELECT @iValue * @iValue AS Result
GO
SELECT Item, EXECUTE usp_SquareIt Item AS Squared ---- can this be done FROM tbA GO
I need to run a stored procedure on all the results of a select query.For a simplified example, I'd like to be able to do something likethis:SELECT JobID, QtyToAddFROM JobsWHERE QtyToAdd > 0Then for the results of the above:EXEC sp_stockadd @JobID, @QtyToAddWhere the above line ran for every result from the above select query.Anyone able to shed some light on how to do this?Many thanks,James
So I have simply procedure: Hmmm ... tak sobie myślę i ...
Czy dałoby radę zrobić procedurę składową taką, że pobiera ona to ID w ten sposob co napisalem kilka postow wyzej (select ID as UID from aspnet_users WHERE UserID=@UserID) i nastepnie wynik tego selecta jest wstawiany w odpowiednie miejsca dalszej procedury ?[Code SQL]ALTER procedure AddTopic @user_id int, @topic_katId int, @topic_title nvarchar(256), @post_content nvarchar(max)as insert into [forum_topics] (topic_title, topic_userId,topic_katId) values (@topic_title, @user_id, @topic_katId) insert into [forum_posts] (topic_id, user_id,post_content) values (scope_identity(), @user_id, @post_content)Return And I want to make something more. What I mean - in space of red "@user_id" I want something diffrent. I make in aspnet_Users table new column uID which is incrementing (1,1). In this way I have short integer User ID (not heavy GUID, but simply int 1,2,3 ...). I want to take out this uID by this :SELECT uID from aspnet_Users WHERE UserId=@UserId.I don't know how can I put this select to the stored procedure above. So I can have a string (?) or something in space of "@user_id" - I mean the result of this select query will be "@user_id". Can anyone help me ? I tried lots of ways but nothing works.
hi need help do this i have table that generate Serial i get the code from this link http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2734123&SiteID=1
and i must to get the same sn + date so whan i do this
Code Snippet select * from tbl_1a WHERE (CONVERT(DATETIME, CONVERT(VARCHAR(16), dateinb, 120)) = CONVERT(DATETIME, CONVERT(VARCHAR(16), CONVERT(DATETIME, '05/05/2008 14:51'), 120))) and sn=0000009 select * from tbl2_2B WHERE (CONVERT(DATETIME, CONVERT(VARCHAR(16), dateinb, 120)) = CONVERT(DATETIME, CONVERT(VARCHAR(16), CONVERT(DATETIME, '05/05/2008 14:51'), 120))) and sn=0000009
i get the current state DATE + SN
i need to insert into other 2 table this Serial+date like this --need help fix this code
Code Snippet DECLARE @sn DECLARE @date set @date =Getdate() -- insert 1 new records tb sn INSERT INTO ConfirmationNumber([ConfirmationDate]) SELECT GETDATE() ---select last value sn set @sn = SELECT top 1 SequenceNumber FROM vw_ConfirmationNumber ---- get the last sn order by SequenceNumber desc --insert into tb1 insert into tbl_1a (fld1,fld2,sn,date) select from tbl_2a fld1,fld2,@sn,@date --insert into tb2 insert into tbl2_2B (f1,f2,date) select from tbl2_1B f1,f2,@sn,@date
Is it possible to have an entire sql select statement as the input variable to a stored procedure? I want the stored procedure to execute the select statement.
ie.
exec sp_SomeFunc 'select * from table1 where id=1'
It may sound weird, but I have my reason for wanting to do it this way. Is this possible? if so, how do I implement this inside the stored procedure?
I have a list of articles stored in my sqlserver 2000 db. To get all the articles I have a stored procedure which just contains a simple select statement. When a user click to download an article, I want to increase the download count. I can do this with a separate stored procedure with an insert statement and passing in the document id, but I'm sure I could do it within one stored procedure, just not sure how. This is my current sp for getting the articles.CREATE PROCEDURE [sp_GetArticles] ASSELECT [DocumentID], [Title], [Description], [Downloads], [UploadDate], [Filesize] FROM Documents WHERE CategoryID = 1
In a stored procedure, I've created a temp table, and am trying to populate it using: insert #myTable exec p_someProc
p_someProc returns 2 record sets, the first is the one I want to stuff into #myTable, the second is a single field. I think that is the reason that I'm getting: "
Insert Error: Column name or number of supplied values does not match table definition.
"
#myTable has the same structure as the first record set. Is that second recordset causing the error, if so, how to I either incorporate it into the temp table or just ignore it?
Hi all,I have created this simple Stored procedure. But it gives me wrong result when I pass a parameter to it. But if I hard code it, it gives me the right result. The I check if the field value of 'Email' is exactly equal to the parameter '@Email'. The field 'Email' is varchar, and CID is integer. CREATE PROCEDURE EmailExists @Email varcharASSELECT CIDFROM CustomersWHERE Customers.Email = @Emailreturn Instead, if I check the value directly, it gives me correct answer. The the following code works fine when I typethe Email directly in the code. CREATE PROCEDURE EmailExists @Email varcharASSELECT CIDFROM CustomersWHERE Customers.Email = 'tomyseba@yahoo.com' return Can anyone tell me the reason for it. Thanking you in advance
Hi, [I'm using VWD Express (ASP.NET 2.0)] Please help me understand why the following code, containing an inline SQL SELECT query (in bold) works, while the one after it (using a Stored Procedure) doesn't:
Select columnname from tablename order by ordercolumn
We will call that "sp_foldersOfFile". It takes 1 parameter, a fileID (int) value.
The result when I execute this from within Management Studio is a single column of 1 to n rows. I want to use these values in another stored procedure like this:
Select @userCount = COUNT(*) from permissions where UserID = @userID and (projectid = @projectID or projectid=0) and clientid = @clientID and folderpermissions in (dbo.sp_FoldersOfFile(@fileID))
The Stored Procedure compiles but it does not query the folderpermissions in the selected values from the sp_FoldersOfFile procedure. I'm sure it is a syntax issue.
I have a stored procedure on a SQL Server 2008 database. The stored procedure is very simple, just a SELECT statement. When I run it, it returns 422 rows. However, when I run the SELECT statement from the stored procedure, it returns 467 rows. I've tried this by running both the stored procedure and the SELECT statement in the same SSMS window at the same time, and the behavior is the same. The stored procedure is:
USE [REMS] GO /****** Object: StoredProcedure [mobile].[GetAllMobileDeviceUsers] Script Date: 12/04/2014 */ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON
[Code] ....
When I do this in the same SSMS window:
exec mobile.GetAllMobileDeviceUsers
SELECT ee.EmployeeID, EmployeeName = LastName + ', ' + FirstName FROM EmployeeInvData ee --UNION
[Code] ....
I get two result sets. The first is 422 rows; the second is 467 rows. Why?
I have a stored procedure that I have written that manipulates date fields in order to produce certain reports. I would like to add a column in the dataset that will be a join from another table (the table name is Periods).
USE [International_Forecast_New] GO /****** Object: StoredProcedure [dbo].[GetOpenResult] Script Date: 01/07/2014 11:41:35 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO
[Code] ....
What I need is to add the period, quarter and year to the dataset based on the "Store_Open" value.
For example Period 2 looks like this Period Quarter Year Period Start Period End 2 1 20142014-01-27 2014-02-23
So if the store_open value is 02/05/2014, it would populate Period 2, Quarter 1, Year 2014.
I have this stored procedure which works fine but I am unsure if I should be doing anything different to improve the overall efficiency of it or any other things that may need changing to shorten the code etc.
It takes in some parameters from an ASP page and puts them through some conditional statements.
Here it is:
CREATE PROCEDURE [imagineDB].[sp_treatment] -- parameters @id int = 0, @ikeyword int = 0, @keyword nvarchar(100) = NULL AS
if @id <> 0 OR @ikeyword = 1
if @id <> 0 and @ikeyword = 0 begin SET NOCOUNT ON; SELECT id, pageTitle, description FROM ihcProcedures where id = @id order by pageTitle ASC end
if @id = 0 and @ikeyword = 1 begin SET NOCOUNT ON; SELECT id, pageTitle, description FROM ihcProcedures where description like '%' + @keyword + '%' order by pageTitle ASC end
if @id <> 0 and @ikeyword = 1 begin SET NOCOUNT ON; SELECT id, pageTitle, description FROM ihcProcedures where id = @id and description like '%' + @keyword + '%' order by pageTitle ASC end
else
if @id = 0 and @ikeyword = 0 begin SET NOCOUNT ON; SELECT id, pageTitle, description FROM ihcProcedures order by pageTitle ASC end
else
if @id = 0 and @ikeyword = 1 begin SET NOCOUNT ON; SELECT id, pageTitle, description FROM ihcProcedures where description like '%' + @keyword + '%' order by pageTitle ASC end
thanks in advance
I want to build a spaceship with ligthspeed capabilities and I don't even know what a wrench is.
I know that <asp:SqlDataSource> is fast, but is it a good practice to use this. The sqldatasource is right there in the aspx page and the SQL for sqldatasource is also right there.Does that not break the 3 tier model, as normally we call a database layer and we have all the SQL's there. In one of the code in saw the following. I don't know if it is a good practice to do something like this. <asp:DropDownList ID="CountryDropDownList" runat="server" DataSourceID="SqlDataSource2" DataTextField="PickListLabel" DataValueField="PickListValue" OnDataBound="hidePanel"> </asp:DropDownList><asp:SqlDataSource ID="SqlDataSource2" runat="server" ConnectionString="<%$ ConnectionStrings:webrepos %>" SelectCommand="SELECT [PickListLabel], [PicklistValue] FROM [PickList_Values] WHERE (([ObjectName] = @ObjectName) AND ([FieldName] = @FieldName)) ORDER BY [AbbrCode]"> <SelectParameters> <asp:Parameter DefaultValue="Account" Name="ObjectName" Type="String" /> <asp:Parameter DefaultValue="Country__c" Name="FieldName" Type="String" /> </SelectParameters> </asp:SqlDataSource>
