Just wondering if i could get some help with this,
I have a large table of decimal values, many of the columns contain only zero values. I would like to write a
stored procedure which loops through these columns, takes the SUM and if it returns zero , then deletes the column from the table.
Any help would be appreciated, thanks in advance, this site has already helped me heaps
Hello, everyone:
There are some commented notes in the stored procedure from clients. Is there any way to remove these notes automatically, I mean using scripts etc.
Thanks
ZYT
I have a SQL 2012 database that has 10 tables. One of the tables is populated by manual import from CSV file. Each time a user calls custom ASP.NET code., records get inserted into a table called forecast_data with incremental increase in FileID. So first import has FileID of 1, second import has FileID of 2 etc.
Structure:
TABLE [dbo].[forecast_data](
[recID] [bigint] IDENTITY(1,1) NOT NULL,
[FileID] [int] NULL,
[Project_Name] [nvarchar](255) NULL,
[Stoplight_Status] [nvarchar](255) NULL,
[Country] [nvarchar](255) NULL,
[Region] [nvarchar](255) NULL,
[code]....
What I am trying to do is only keep the data that has the highest FileID (MAX(FileID). I would like to write a store procedure that removes all older data once a new import is written into the table.
I currently have a process that has a cursor. It takes data and executes a stored procedure with parameters.
declare @tmpmsg varchar(max)
declare @tmpmsgprefix varchar(max)
declare @cms varchar(20)
create table #tmpIntegrity(matternum varchar(10),ClientName varchar(20))
insert into #tmpIntegrity(matternum,ClientName)
[Code] ....
Output from code:
The following Client1 accounts have A1 value and a blank A2 field. Accounts: Ac1,Ac2,Ac3,Ac4,
The following Client2 accounts have A1 value and a blank A2 field. Accounts: Ac1,Ac2,Ac3,
The following Client3 accounts have A1 value and a blank A2 field. Accounts: Ac1,Ac2,Ac3,
The following Client4 accounts have A1 value and a blank A2 field. Accounts:
Desired output (no trailing comma):
The following Client1 accounts have A1 value and a blank A2 field. Accounts: Ac1,Ac2,Ac3,Ac4
The following Client2 accounts have A1 value and a blank A2 field. Accounts: Ac1,Ac2,Ac3
The following Client3 accounts have A1 value and a blank A2 field. Accounts: Ac1,Ac2,Ac3
The following Client4 accounts have A1 value and a blank A2 field. Accounts:
Next, how do I call the stored procedure without doing it RBAR? Is that possible?
execute usp_IMessage 832,101,@tmpmsgprefix,@tmpmsg,','
I have set up an OLEDB data source with Command Text that calls a stored procedure. The rows and columns of data appear correctly when I preview the data but no external columns are available for mapping to output columns.
I was hoping to call the stored procedure and output the resulting data to an Excel destination. Am I tryig to do sornething that can't be done?
Thanks,
*** Campbell
I have an application that I inherited, and I have a annoying problem. We're using stored procedures to return most of our data, and occasionally we receive errors stating that a particular column cannot be found in the resulting data table. When I run the stored procedure against SQL Server I receive the expected output. What would make this random act happen, any ideas?
Also, I keep receiving errors stating that a connection is already open and needs to be closed before an action to the database is performed. I'm explicitly closing each connection in a finally block for every method in my data access code, so a connection should always be closed, right?
The SQL below is the start of a massive Stored Procedure for Comparing two Datasets, which will be produced onto a report.
I was wondering if I could call an SQLserver Procedure that would tell me the names of all the Columns that are produced by this SP, so I can print them out and more easily code the report?
SELECT
stk.StockNumber,
stk.DefaultImageName,
tVTP.Make as PolMake,
stkV.VehicleMake,
tVTP.Model as PolModel,
stkV.VehicleModel,
tVTP.ModelNo as PolModelNo,
stkV.VehicleModelNo,
tVTP.EngineNumber as PolEngineNumber,
Stk.EngineNumber,
tVTP.comHeadLightNumber as PolHeadLightNumber,
Stk.comHeadLightNumber,
tVTP.comTailLightNumber as PolTailLightNumber,
Stk.comTailLightNumber ,
tVTP.comBumperLightNumber as PolBumperLightNumber,
Stk.comBumperLightNumber,
tVTP.comCornerLightNumber as PolCornerLightNumber,
Stk.comCornerLightNumber,
tVTP.Chassis as PolChassis,
--Drive Train
tVD.DriveTrainDescription as POlDriveTrainDescription,
StktVD.DriveTrainDescription,
--Body Type
tVBT.BodyTypeDescription as PolBodyDescription ,
StkVBT.BodyTypeDescription
FROM tblStock Stk
--JOINS FOR THE Policy Definition
INNER JOIN tblVehicles V
ON V.VehicleID = Stk.VehicleID
INNER JOIN tblVehicleType_Policy tVtP
ON tVTP.VehicleMaster = V.VehicleMaster
AND tVTP.Make = V.VehicleMake
AND tVTP.Model = V.VehicleModel
AND tVTP.ModelNo = V.VehicleModelNo
INNER JOIN tblVehicleDriveTrain tVD ON
tVD.vehicleDrivetrainID = tVTP.DrivetrainID
INNER JOIN tblvehicleBodyType tVBT ON
tVBT.VehicleBodyTypeID = tVTP.BodyTypeID
--JOINS FOR the Stock Definition
INNER JOIN tblVehicles STkV ON
StkV.VehicleID = Stk.VehicleID
INNER JOIN tblvehicleBodyType StkVBT ON
StkVBT.VehicleBodyTypeID = Stk.BodyTypeID
INNER JOIN tblVehicleDriveTrain StktVD ON
StktVD.vehicleDrivetrainID = stk.DrivetrainID
--INNER JOIN tbl
WHERE Stk.StockNumber LIKE 'V%'
Hello,I've written an insert trigger to fill in data on 5 columns based on the keyfield column after a record is added to a table. The trigger works fine.But what I also want to do is to write a stored procedure that will updatethe 5 columns for the entire table based on the table key field column. I'mnew to both triggers and stored procedures and I can't figure out how tomake a stored procedure do what I want.Can someone help me to get me started? Here is the trigger code that I amtrying to get to work as a stored procedure. All I'm trying to do is tobreak up an entry that contains dashes into separate fields that aredelimited by the dashes. As an example if field CABLENO is equal toI-IJB-200-45, then I want to break that up into 4 separate fields containingI, IJB, 200, 45 respectively.Thanks,Al Willis--------------------------------------------------------------------------------------------------------IF EXISTS (SELECT NAME FROM SYSOBJECTS WHERE NAME='CABLE_SEGMENTS' AND TYPE='TR')DROP TRIGGER IM.CABLE_SEGMENTSGOCREATE TRIGGER CABLE_SEGMENTSON IM.CAB_MFOR INSERTASDECLARE @CABLENO_REMAIN VARCHAR(40),@DASH_POS SMALLINT,@SEG1 VARCHAR(40),@SEG2 VARCHAR(40),@SEG3 VARCHAR(40),@SEG4 VARCHAR(40),@SEG5 VARCHAR(40)SELECT @CABLENO_REMAIN = CABLENO FROM INSERTEDSELECT @SEG1 = NULLSELECT @SEG2 = NULLSELECT @SEG3 = NULLSELECT @SEG4 = NULLSELECT @SEG5 = NULLSELECT @DASH_POS = CHARINDEX('-',@CABLENO_REMAIN)IF @DASH_POS 0BEGINSELECT @SEG1 = SUBSTRING(@CABLENO_REMAIN,1,@DASH_POS-1)SELECT @CABLENO_REMAIN = SUBSTRING(@CABLENO_REMAIN,@DASH_POS+1,40)SELECT @DASH_POS = CHARINDEX('-',@CABLENO_REMAIN)IF @DASH_POS 0BEGINSELECT @SEG2 = SUBSTRING(@CABLENO_REMAIN,1,@DASH_POS-1)SELECT @CABLENO_REMAIN = SUBSTRING(@CABLENO_REMAIN,@DASH_POS+1,40)SELECT @DASH_POS = CHARINDEX('-',@CABLENO_REMAIN)IF @DASH_POS 0BEGINSELECT @SEG3 = SUBSTRING(@CABLENO_REMAIN,1,@DASH_POS-1)SELECT @CABLENO_REMAIN =SUBSTRING(@CABLENO_REMAIN,@DASH_POS+1,40)SELECT @DASH_POS = CHARINDEX('-',@CABLENO_REMAIN)IF @DASH_POS 0BEGINSELECT @SEG4 = SUBSTRING(@CABLENO_REMAIN,1,@DASH_POS-1)SELECT @CABLENO_REMAIN =SUBSTRING(@CABLENO_REMAIN,@DASH_POS+1,40)SELECT @SEG5 = @CABLENO_REMAINENDELSESELECT @SEG4 = @CABLENO_REMAINENDELSESELECT @SEG3 = @CABLENO_REMAINENDELSESELECT @SEG2 = @CABLENO_REMAINENDELSESELECT @SEG1 = @CABLENO_REMAINUPDATE IM.CAB_M SET CABLESEG1 = @SEG1,CABLESEG2 = @SEG2,CABLESEG3 =@SEG3,CABLESEG4 = @SEG4,CABLESEG5 = @SEG5FROM INSERTED INSWHERE IM.CAB_M.CABLENO = INS.CABLENOGO--------------------------------------------------------------------------------------------------------
View 2 Replies View RelatedI want to have a Stored procedure call another stored procedure gathering the info from some of the information SP#1 and use it calling SP#2...
For example sp_Help returns columns when executed (Name, Owner, Object_type).
So how can I reference the Name column and pass it to SP#2
Exec sp_Help
...?
SP#2 name=sp_help.Name ???
Thanks
Richard
How can I find the result columns from code? Visual J++ Seems to be able to do that. ADO doesn't want to do it for me, neither does ODBC's SQLProcedureColumns().
Any solutions?
hi,
i have the following stored proc which returns a resultset at the end, i have an SSIS package that calls this stored proc and outputs the result to a file. However, the package fails because it cannot pull the columns for the schema because of the return lines in the middle of the stored proc. If i remove the it works fine, pulls hte columns as normal. but if i leave them in ssis cannot get the columns. what can i do to get around this?
ALTER PROCEDURE [dbo].[uspCreateBrightPointFile]
AS
SET nocount ON
IF EXISTS (SELECT TOP 1 *
FROM brightpointfile)
TRUNCATE TABLE brightpointfile
-- Get the order information from the database where vendorconfirmationID = 0
INSERT INTO brightpointfile
SELECT o.orderid,
o.requestid,
'295193' AS araccountnumber,
o.orderdate,
'PRIORITY' AS shipmethod,
'Asurion Dobson' AS billname,
'PO Box 110808' AS billaddress1,
'Attn Account Receivable' AS billaddress2,
' ' AS billaddress3,
'Nashville' AS billcity,
'TN' AS billstate,
'37222' AS billzip,
c.fullname AS shipname,
Replicate(' ',100) AS shipaddress1,
Replicate(' ',100) AS shipaddress2,
' ' AS shipaddress3,
Replicate(' ',40) AS shipcity,
' ' AS shipstate,
Replicate(' ',10) AS shipzip,
'1' AS linenumber,
ve.sku AS itemcode,
o.quantity AS qty,
r.typeid,
r.customerid,
0 AS addressfound
FROM [order] o WITH (NoLock)
JOIN request r WITH (NoLock)
ON o.requestid = r.requestid
JOIN customer c WITH (NoLock)
ON r.customerid = c.customerid
JOIN (SELECT [subequipid],
[subid],
[clientequipid],
[serialno],
[statusid],
[startdate],
[enddate],
[createdate],
[createuserid]
FROM subequip s1 WITH (NoLock)
WHERE s1.statusid = 1
AND s1.startdate = (SELECT MAX(s2.startdate)
FROM subequip s2 WITH (NoLock)
WHERE s2.statusid = 1
AND s2.subid = s1.subid)) se
ON r.subid = se.subid
JOIN vendorequip ve WITH (NoLock)
ON se.clientequipid = ve.clientequipid
JOIN clientequip ce WITH (NoLock)
ON ce.clientequipid = se.clientequipid
WHERE vendorconfirmationid IS NULL -- order was never sent to CellStar
AND ve.typeid IN (1) -- only pull direct fulfillment (not store fulfillment)
AND ve.vendorid IN (2) -- only pull vendor = CellStar Insurance
AND o.orderdate > '2007-08-01' -- only pull orders after 08/01/2007
--IF @@ERROR <> 0
--RETURN 1
--First use the address types of 2
UPDATE brightpointfile WITH (ROWLOCK)
SET shipaddress1 = b.address,
shipaddress2 = b.address2,
shipcity = b.city,
shipstate = b.stateid,
shipzip = b.zipcode,
addressfound = 1
FROM customeraddress a WITH (NOLOCK),
address b WITH (NOLOCK)
WHERE a.customerid = brightpointfile.customerid
AND b.addressid = a.addressid
AND a.typeid = 2
--IF @@ERROR <> 0
--RETURN 2
--Update the rest where the address type is 1 and there is no type 2
UPDATE brightpointfile WITH (ROWLOCK)
SET shipaddress1 = b.address,
shipaddress2 = b.address2,
shipcity = b.city,
shipstate = b.stateid,
shipzip = b.zipcode
FROM customeraddress a WITH (NOLOCK),
address b WITH (NOLOCK)
WHERE a.customerid = brightpointfile.customerid
AND b.addressid = a.addressid
AND a.typeid = 1
AND brightpointfile.addressfound = 0
--IF @@ERROR <> 0
--RETURN 3
--Select all the records from the temp table, plus union in 2 extra records required by the client
SELECT *
FROM (SELECT orderid,
requestid,
araccountnumber,
orderdate,
shipmethod,
billname,
billaddress1,
billaddress2,
billaddress3,
billcity,
billstate,
billzip,
shipname,
shipaddress1,
shipaddress2,
shipaddress3,
shipcity,
shipstate,
shipzip,
1 AS linenumber,
itemcode,
qty,
0 AS additional
FROM Brightpointfile WITH (nolock)
UNION ALL
SELECT orderid,
requestid,
araccountnumber,
orderdate,
shipmethod,
billname,
billaddress1,
billaddress2,
billaddress3,
billcity,
billstate,
billzip,
shipname,
shipaddress1,
shipaddress2,
shipaddress3,
shipcity,
shipstate,
shipzip,
brightpointdefaultsku.linenumber,
brightpointdefaultsku.sku,
1,
1 AS additional
FROM Brightpointdefaultsku WITH (nolock),
Brightpointfile WITH (nolock)) a
ORDER BY orderid,
additional
IF @@ERROR <> 0
RETURN 1
RETURN 0
I am imagining something you might pass the names of 2 stored procs (an old version and new one), and a query to produce valid parameters. It would then fire off each proc for a set number of executions, while storing off the results in temp tables, and at the end it would do a data compare, and store off performance data from dynamic management views.
Now I know how to get the parameters for a stored procedure out of the catalogue views, but is SQL Server aware at all of the schema of the results of stored procedures that return result sets, becuase I was thinking of doing something like...
INSERT INTO #datacompare(col1,col2)
EXEC mystoredprocedure
... but I can not seem to figure out how to dynamically gather the schema of the result set.
I have a question. I know its possible to create a dynamic query and do an exec @dynamicquery. Question: Is there a simpler way to update specific columns in a table at run time without doing if else for each column to determine which is null and which has a value because i'm running into a design dilemna on how to go about it.
FYI: All columns in the table design are set to null by default.
Thanks in advance,
Dimeji
We are facing an issue while executing a stored procedure which uses a table of current database with INNER JOIN a table of another database in same instance.
Per our requirement, we are inserting select statement output in table variable. Then applying business logic and finally showing the data from table variable.
This scenario is working exactly fine in Dev environment. But when we deployed the code in quality environment. Stored procedure does not returning OUTPUT/ (No column names) from table variable.
During initial investigation, we found that collation of these two databases are different but we added DATABASE_DEFAULT collation in the JOIN.
I have a stored procedure which returns a result set as follows:
(Headers)Total,WV1,WV2,WV3,WV4,WV5.....
(Example data) "Some total name",1,2,3,4,5.....
The WV1, WV2, WV3 column names will be different depending on parameters passed to the stored procedure. In other words, the column names or number of columns aren't fixed (apart from "Total").
What I would like to be able to do is to just force SSRS to use the column headers supplied by the stored procedure as the column names in the report.
I admit I am a novice to queries and stored procedures but understand stored procedures are more secure, so I am choosing to challenge myself with a stored procedure and have not been successful at all.
What I am looking for is a stored procedure which would query the single table named Hospital, which would search from a single textbox the end user would enter the text they are searching for on a Windows VB application but is dependant on a few end user selected items.
1. The end user can select a radio button either "Starts with" or "Contains"
2. The end user can select from 6 checkboxes (Hospitals, Address, City, State, Zip, Type) to limit or expand their search from the columns in the table.
The table has 17columns (CO, PARENTCO, ADDR, POBox, CITY, State, ZIP, TEL, FAX, WEBSITE, HOSP_TYPE, OWNERSHIP, BEDS, CONTACT, TITLE, Email) but I am only allowing the end user to search by the above 6 columns and need to display the above 6 columns plus the phone, fax and website columns.
After the user clicks the search button it displays the results in a listbox and the user will then select the one they want and it displays the information in another set of textboxes.
Hopefully someone can get me pointed in the right direction! Thanks for your help!
I am using execute sql task to run a stored procedure in oracle database which returns a resultset. This works. Now I need to send the ouput to a destination table in a sql database. Should I use for each loop to pick the resultset and insert it into the destination one by one (which I dont think is a great idea) or is there a better way to accomplish this task (in data flow task) ?
When I use dataflow task instead of execute sql task, the main issue is I am not able to see the output columns when I execute an oracle stored procedure, but when I see the preview I can see the resultset . But I can see the output columns for a sql server stored procedure.
One of our users tried to set replication solution in a sqlserver (the idea was given up).
SQL Server added in each table a column related to replication.
We want to remove theses columns and I used the following script :
select 'ALTER TABLE dbo.'+object_name(id)+' DROP CONSTRAINT '+object_name(constid)+' GO'
+'ALTER TABLE dbo.'+object_name(id)+' DROP COLUMN '+'msrepl_tran_version GO'
from sysconstraints where object_name(constid) like '%msrep%'
Question:
1. I want to know how to introduce a carraige return in order to have some thing like this :
...
ALTER TABLE dbo.T_CommandCopyFile
DROP CONSTRAINT DF__T_Command__msrep__44AB0736
GO
ALTER TABLE dbo.T_CommandCopyFile
DROP COLUMN msrepl_tran_version
...
2. Is there any other solution to do this more simply ?
Hello
When you set the results from a query to text, very often you end up with a format like:
job_id name
---------------------------------------------------------------------- ------------------------------------------------------------------------------------------------
Is there a way to get rid of the extra space between two columns? Sometimes it's more than the width of my screen between columns
Thanks!
Worf
I have a table containing the following data:
LinkingIDID1 ID2
166202180659253178
166202253178180659
166334180380253179
166334253179180380
166342180380180659
166342180659180380
166582253179258643
166582258643253179
264052258642258643
264052258643258642
264502258643258663
264502258643259562
Within the LinkingID, there are duplicates in ID1 and ID2 but just in opposite columns. I have been trying to figure out a way to remove these set based. It doesn't matter which duplicate is removed. Essentially these are just endpoints and I don't care which side they are on. The solution must recognize the duplicates and not just remove based on every 2nd row.
We have a database with hundreds of tables, each with "CreatedByLoginId" and "ModifiedByLoginId" FK columns back to the Login table. This is all fine and well, but 500+ tables all link back to Login table every time a record is inserted or updated.
For strictly performance reasons, what do you think of us REMOVING the FK constraints on all of our tables? While this does mean that a GUID that is not a valid LoginId could potentially be put in a table, I'm not too worried about it because users don't have direct access to the database.
I have SSRS report that has around 80+ columns. I have requirement where in dynamically hideshow columns in report based on user selection. I could able to do it by setting expression for "Visiblity" property and having report parameter thro' which columns to display can be choosen.
My problem is 2 points
1. fox example if columns 2 and 4 to be hidden, then there is an empty column between 1 and 3 and 5 columns. How to avoid this
2. When i export to PDF / Excel these spaces prevail.
I've created a SSIS package which takes a matrix from Excel file and insert into SQL table. It works perfectly! However, if I would add a new column into that matrix in Excel. Unpivot tool should take into process dynamically. Is there a way to provide this automatically?
View 4 Replies View RelatedHi 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?
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.
I am new to work on Sql server,
I have One Stored procedure Sp_Process1, it's returns no of columns dynamically.
Now the Question is i wanted to get the "Sp_Process1" procedure return data into Temporary table in another procedure or some thing.
I have some code that I need to run every quarter. I have many that are similar to this one so I wanted to input two parameters rather than searching and replacing the values. I have another stored procedure that's executed from this one that I will also parameter-ize. The problem I'm having is in embedding a parameter in the name of the called procedure (exec statement at the end of the code). I tried it as I'm showing and it errored. I tried googling but I couldn't find anything related to this. Maybe I just don't have the right keywords. what is the syntax?
CREATE PROCEDURE [dbo].[runDMQ3_2014LDLComplete]
@QQ_YYYY char(7),
@YYYYQQ char(8)
AS
begin
SET NOCOUNT ON;
select [provider group],provider, NPI, [01-Total Patients with DM], [02-Total DM Patients with LDL],
[Code] ....
I have a requirement to execute an Oracle procedure from within an SQL Server procedure and vice versa.
How do I do that? Articles, code samples, etc???
I have a sub that passes values from my form to my stored procedure. The stored procedure passes back an @@IDENTITY but I'm not sure how to grab that in my asp page and then pass that to my next called procedure from my aspx page. Here's where I'm stuck: Public Sub InsertOrder() Conn.Open() cmd = New SqlCommand("Add_NewOrder", Conn) cmd.CommandType = CommandType.StoredProcedure ' pass customer info to stored proc cmd.Parameters.Add("@FirstName", txtFName.Text) cmd.Parameters.Add("@LastName", txtLName.Text) cmd.Parameters.Add("@AddressLine1", txtStreet.Text) cmd.Parameters.Add("@CityID", dropdown_city.SelectedValue) cmd.Parameters.Add("@Zip", intZip.Text) cmd.Parameters.Add("@EmailPrefix", txtEmailPre.Text) cmd.Parameters.Add("@EmailSuffix", txtEmailSuf.Text) cmd.Parameters.Add("@PhoneAreaCode", txtPhoneArea.Text) cmd.Parameters.Add("@PhonePrefix", txtPhonePre.Text) cmd.Parameters.Add("@PhoneSuffix", txtPhoneSuf.Text) ' pass order info to stored proc cmd.Parameters.Add("@NumberOfPeopleID", dropdown_people.SelectedValue) cmd.Parameters.Add("@BeanOptionID", dropdown_beans.SelectedValue) cmd.Parameters.Add("@TortillaOptionID", dropdown_tortilla.SelectedValue) 'Session.Add("FirstName", txtFName.Text) cmd.ExecuteNonQuery() cmd = New SqlCommand("Add_EntreeItems", Conn) cmd.CommandType = CommandType.StoredProcedure cmd.Parameters.Add("@CateringOrderID", get identity from previous stored proc) <------------------------- Dim li As ListItem Dim p As SqlParameter = cmd.Parameters.Add("@EntreeID", Data.SqlDbType.VarChar) For Each li In chbxl_entrees.Items If li.Selected Then p.Value = li.Value cmd.ExecuteNonQuery() End If Next Conn.Close()I want to somehow grab the @CateringOrderID that was created as an end product of my first called stored procedure (Add_NewOrder) and pass that to my second stored procedure (Add_EntreeItems)
View 9 Replies View RelatedI have a stored procedure and in that I will be calling a stored procedure. Now, based on the parameter value I will get stored procedure name to be executed. how to execute dynamic sp in a stored rocedure
at present it is like EXECUTE usp_print_list_full @ID, @TNumber, @ErrMsg OUTPUT
I want to do like EXECUTE @SpName @ID, @TNumber, @ErrMsg OUTPUT
I managed to transpose rows into columns.
;WITH
ctePreAgg AS
(
select top 500 act_reference "ActivityRef",
row_number() over (partition by act_reference order by act_reference) as rowno,
t3.s_initials "Initials"
from mytablestuff
order by act_reference
[code]...
But what I would love to do next is take each of the above rows - and return the initials either in one column with all the nulls and duplicate values removed, separated by a comma ..
ref, initials
Ag-4xYS
Ag-6xYS,BL
Ap-1xKW
At-2x SAS,CW
At-3x SAS,CW
OR the above but using variable number of columns based on the maximum number of different initials for each row.this is not strictly required, but maybe neater for further work on the view
ref, init1,init2
Ag-4xYS
Ag-6xYS,BL
Ap-1xKW
At-2x SAS,CW
At-3x SAS,CW
I have a stored procedure that calls a msdb stored procedure internally. I granted the login execute rights on the outer sproc but it still vomits when it tries to execute the inner. Says I don't have the privileges, which makes sense.
How can I grant permissions to a login to execute msdb.dbo.sp_update_schedule()? Or is there a way I can impersonate the sysadmin user for the call by using Execute As sysadmin some how?
Thanks in advance
Has anyone encountered cases in which a proc executed by DTS has the following behavior:
1) underperforms the same proc when executed in DTS as opposed to SQL Server Managemet Studio
2) underperforms an ad-hoc version of the same query (UPDATE) executed in SQL Server Managemet Studio
What could explain this?
Obviously,
All three scenarios are executed against the same database and hit the exact same tables and indices.
Query plans show that one step, a Clustered Index Seek, consumes most of the resources (57%) and for that the estimated rows = 1 and actual rows is 10 of 1000's time higher. (~ 23000).
The DTS execution effectively never finishes even after many hours (10+)
The Stored procedure execution will finish in 6 minutes (executed after the update ad-hoc query)
The Update ad-hoc query will finish in 2 minutes