T-SQL (SS2K8) :: Converting A Stored Procedure Into A Query?
Aug 5, 2014
I have a rather strange requirement where I need to convert my stored procedure into a normal SQL query for generating a SSRS report.
The business is very specific not to use SP or temp table in the query for the report generation.
I have a stored proc where I have used temp table and I have inserted values into that table.
what all these things I need to take when I convert it to as a SQL query for report generation?
View 9 Replies
ADVERTISEMENT
Nov 9, 2001
I am switching my database from MS access to SQL server, and i want the following query to br converted to SQL stored procedure
CREATE PROCEDURE FORUM_MESSAGE AS
SELECT *
FROM FORUM_MESSAGES
WHERE ID=MessageID;
here "MessageID" is a run time generated parameter, and is not a field in the database.
thanx
View 1 Replies
View Related
May 30, 2015
I have a stored proc that is being executed from an OLE DB Source component in my Data Flow. Takes one input parm, has several variables declared within, a derived table which is used in a join and all within a try catch block with transaction handling. No updates, just returning data, works great, except now I have been asked to replace these stored procs with inline queries.
ALTER PROCEDURE [dbo].[usp_Get_Test]
(
@numberOfMonthsint
)
AS
BEGIN
SET NOCOUNT ON
set transaction isolation level read uncommitted
[Code] ....
The problems I have run into so far are...
SQL command text in OLE DB Source Editor does not like:
- TRY/CATCH block
- Will not let me use my input parm (@numberOfMonths int)
- When I hard code in my input parm (select @BeginDate = dateadd(MONTH, -1, GETDATE())) I can parse query and run the step but no results are returned. So I am let to assume that it does not like the @TESTY derived table.
The query here as a sample has had pivots removed as well, but research suggests this should be an issue in the SQL command text.
Also, I know not even to try the Build Query... cause it will complain about any variable declarations (i.e., declare @BeginDate datetime).
For instance, can I pull this off with an Execute SQL Task? The problem is I don't see this available in the toolbox for the Data Flow.
Also, would my error handling be done in the Event Handlers tab now and if so, is there a good example of this?
View 3 Replies
View Related
May 13, 2014
Say I have to server, A and B.
Now I want to write a stored procedure in server A, to retrieve data from server B.
How could I manage to do that?
View 4 Replies
View Related
Nov 14, 2014
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.
View 1 Replies
View Related
Oct 10, 2014
I need to convert a OUTER APPLY hint in my query to LEFT JOIN.How it can be done?The code which is presently is this: OUTER APPLY Additional Fields. nodes('/AdditionalFields/AdditionalField') AS AF (C)
View 4 Replies
View Related
Sep 1, 2014
I have the following code and i want to passed more than one value:
DECLARE @myvendedor AS varchar(255)
SET @myvendedor = '87,30'
print @myvendedor
SELECT top 10 ECOM.COM1,* from ecom (nolock) WHERE ecom.PORVEND=1 AND ECOM.VENDEDOR IN (@myvendedor)
Table Field ECOM.VENDEDOR is Numeric(4,0)
This error occur:
87,30 --Result of PRINT
Msg 8114, Level 16, State 5, Line 6
Error converting data type varchar to numeric.
I change :
DECLARE @myvendedor AS numeric(4,0)
and this error appear:
Msg 8114, Level 16, State 5, Line 2
Error converting data type varchar to numeric.
View 9 Replies
View Related
Jun 5, 2005
CREATE PROCEDURE procCreateBasket
@ShopperID int,
@BasketID int OUTPUT
AS
INSERT INTO Basket(ShopperID)
VALUES (@ShopperID)
SELECT @BasketID = @@IDENTITYI don't want to use this as a stored procedure I want to convert it as a string an use in that way in my asp.net application how can I do that?
View 2 Replies
View Related
Sep 2, 2005
I have the following code in C# that I would like to port to a Stored Procedure.
The problem is it's a query built on many factors.
Here's the code:
Code:
DataAccess.Connection conList = new Connection();
string strWhere = "";
string strSQL = "";
if (PackageID > 0)
{
//strWhere += " AND EAItems.PackageID = " + PackageID;
strWhere += " AND EAPackageItems.PackageID = " + PackageID;
}
if (From != DateTime.MinValue)
{
strWhere += " AND LogItem.DateCreated >= " + From;
}
strWhere += " AND LogItem.DateCreated <= " + To;
if (!Closed)
{
strWhere += " AND EAItems.Closed = " + Closed;
}
if (Search != null && Search.Length > 0)
{
strWhere += " AND (EAItems.Number LIKE " + "%" + Search + "%" + " OR EAItems.Description LIKE " + "%" + Search + "%" + ")";
}
strSQL = "Big SQL Statement with 5 inner joins " + strWhere + " ORDER BY Number";
return conList.GetDataTable(strSQL);
View 3 Replies
View Related
Feb 10, 2008
Hi i have the following method in my page, it works fine and everything, but i am trying to modify it so that it can take a stored procedure;
protected void Button1_Click(object sender, EventArgs e)
{SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["streamConnectionString"].ConnectionString);String sql = "SELECT userID, userName FROM users WHERE userName LIKE " + "'" + userName.Text + "%' OR organisation= " + "'" + OrganisationList.SelectedValue + "'";
conn.Open();
SqlCommand comm = new SqlCommand(sql, conn);SqlDataReader reader = comm.ExecuteReader(CommandBehavior.CloseConnection);
DataList1.DataSource = reader;
DataList1.DataBind();
conn.Close();
}
My question is what should my stored procedure be, and also how will i now structure the above method.
View 5 Replies
View Related
Aug 6, 2006
I need to link together string (NT-) and integer variable (1). The value of variable2 should be NT-1. I always got such an error message:
Conversion failed when converting the varchar value 'NT-1' to data type int.
DECLARE @variable1 int;
SET @variable1 = 1;
DECLARE @variable2 varchar(10);
SET @variable2 = CONVERT(varchar, 'NT-1')+ CONVERT(varchar, @variable1);
View 1 Replies
View Related
Aug 19, 2004
I have a stored procedure called from ASP code, and when it is executed, the stored procedure takes in a date as an attribute.
But since the stored procedure call is really just a string in the code, it is taking in the value as a string. So in my stored procedure, I want to convert a string value to a date value.
Any idea how this is done?
View 1 Replies
View Related
Aug 5, 2015
i have table scripts , i want to create all the tables by passing database name as variable using stored procedure in sql.
View 4 Replies
View Related
Jul 31, 2006
HI Experts...
I am using SQL SERVER 2005 standard edition
I have encountered a problem regarding converting date data type in stored procedure
As i was having problem taking date as input parameter in my stored procedure, so, then I changed to varchar (16) i.e.
CREATE PROCEDURE sp_CalendarCreate
@StDate VARCHAR(16) ,
@EDate VARCHAR(16),
then I am converting varchar to date with following code
DECLARE @STARTDATE DATETIME
DECLARE @ENDDATE DATETIME
SELECT @STARTDATE = CAST(@STDATE AS DATETIME)
SELECT @ENDDATE = CAST(@EDATE AS DATETIME)
When I try to execute the procedure with following code
execute sp_CalendarCreate @stdate='12-1-06',@edate='20-1-06'
but it gives me following error
Msg 242, Level 16, State 3, Procedure sp_CalendarCreate, Line 45
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.
Can any one tell me the solution of that problem (at ur earliest)
regards,
Anas
View 5 Replies
View Related
Apr 8, 2014
I am connecting to a new SQL Server 2008 R2 database using SSMS from my ADMIN VM workstation. I bring up a Stored Procedure and make a change.... I execute the Stored Procedure... after it finishes.... I exit out without saving to a file.... I go back in and my change was not held.
I can do the exact same process with an old SQL Server 2005 database. Is there a permission I am missing to set to be able to do this on the 2008 database.
View 9 Replies
View Related
Oct 16, 2014
yesterday i was trying to create Stored procedure but it fails i don't know why
CREATE proc GetBooksbyBorrowerID @Borrower_id INT
AS
BEGIN
SELECT A.BORROWER_ID ,a.ISBN, b.book_Title,b.LANGUAGE, CONVERT(VARCHAR,a.borrowed_from_date,103)"Borrowed On(dd/mm/yyyy)" FROM borrower_details a, book_mst b
WHERE a.borrower_id=@Borrower_id
AND a.ISBN = b.ISBN
END
GO
EXEC SP_Task1 10001
View 9 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
Apr 16, 2014
In t-sql 2008 r2 I need execute a stored procedure called StudentData and pass 3 parameter values to the stored procedure. The stored procedure will then return 5 values that are needed for the main sql. My problem is I do not know how to have the t-sql call the stored procedure with the 3 parameter values and pass back the 5 different unique data values that I am looking for.
The basic dataset is the following:
SELECT SchoolNumber,
SchoolName,
StudentNumber,
from [Trans].[dbo].[Student]
order by SchoolNumber,
SchoolName,
StudentNumber
I basically want to pass the 3 parameters of SchoolNumber, SchoolName, and StudentNumber to the stored procedure called StudentData from the data I obtain from the [Trans].[dbo].[Student]. The 3 parameter values will be obtained from the sql listed above.
The columns that I need from the stored procedure called StudentData will return the following data columns
that I need for the report: StudnentName, StudentAddress, Studentbirthdate, StudentPhoneNumber, GuardianName.
Thus can you show me how to setup the sql to meet this requirement I have?
View 2 Replies
View Related
May 16, 2014
I am trying to modify a script that to back databsae in t-sql.I put it in a stored procedure with one paramenter dbname.I would like if a dbname is passed when calling the sproc, it will only backup this database, if no paramenter is entered, the dbname is null, then backup all user databases- no system databases.But I have difficulty to define this two situations in code.
Below is the sproc:
CREATE PROCEDURE [dbo].[BackupDB]
@dbname varchar(50)
AS
BEGIN
[code]...
View 1 Replies
View Related
Jun 20, 2014
I'm not new to SQL at all, but I'm completely new to backup/restore TSQL.I have the following script, which backs up 2 databases and restores them under different database names.the script runs fine as-is, but I cannot seem to package it within a create stored procedure statement.if I attempt to wrap create procedure <name> AS begin....end around it, the statement simply executes.
--backup LGTY_QA_01, restore to LGTY_DV_01
BACKUP DATABASE LGTY_QA_01
TO DISK = 'C:Program FilesMicrosoft SQL ServerMSSQL10_50.MSSQLSERVERMSSQLBackupLGTY_QA_01.bak'
WITH FORMAT, CHECKSUM
GO
ALTER DATABASE LGTY_DV_01 SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
[code]...
View 1 Replies
View Related
Aug 29, 2014
I know ran across this some time ago when researching some other topic, but I would like to add an option to our stored procedures (which have multiple parameters) so a user could do something like:
EXEC dbo.usp_MyStoredProcedure - EXEC dbo.usp_MyStoredProcedure -?
The result would just list the parameters in the stored procedure and the options available for each parameter/what the parameter was use for.
Here is an example of one stored procedure in use:
CREATE PROCEDURE [dbo].[usp_MyStoredProcedure]
( @BCP INT = 0---- 0 [Default]: No BCP Import, 1: Insert TempBCP Data
, @Debug INT = 0---- 0 [Default]: Run process, 1: Create temp tables, run queries
, @StartDate smalldatetime = '1/1/1900' ---- Enter StartDate; [Default]: otherwise use Max of Invoice date
, @Date INT = 0 ---- 0 [Default]: Use std process (get last Saturday date from @StartDate), 1: Use @StartDate as is
)
WITH EXECUTE AS 'ADBAccount'
AS
BEGIN
....
END
View 2 Replies
View Related
Oct 15, 2014
Note: @procName,@strAccount,@intHospital,@patType are passed to this procedure from another procedure
Note: The @procname procedure also takes the above parameters and @outDupCheck as output parameter
DECLARE @sqlStr NVARCHAR(500)
DECLARE @ParmDefinition NVARCHAR(500)
DECLARE @parmINAccount VARCHAR(30),@parmINHospId int , @ParmINpatType varchar(1)
DECLARE @parmRET1 int
SET @parmINAccount = @strAccount
SET @parmINHospId = @intHospital
SET @ParmINpatType = @patType
SET @sqlStr = N'Exec ' + @procName + ' @strAccount,@intHospital,@patType, @outDupCheck OUTPUT'
SET @ParmDefinition=N'@strAccount varchar(50),@intHospital int,@patType varchar(1), @outDupCheck int OUTPUT';
EXECUTE sp_executesql @sqlStr, @ParmDefinition, @strAccount = @parmINAccount, @intHospital=@parmINHospId,@patType=@ParmINpatType,@outDupCheck = @parmRET1
SELECT @parmRET1
--The parameter @parmRET1 returns NULL instead of 1 .
The @procName returns value 1 correctly if I run it separately ( outside of the dynamic sql)
Not sure what is wrong here...
View 2 Replies
View Related
Aug 28, 2012
Here's the SP
Code:
USE [byrndb]
GO
/****** Object: StoredProcedure [dbo].[sp_GetLikeJobsByJobNumber] Script Date: 08/28/2012 15:17:28 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
[Code] ....
Why when I run
Code:
USE [byrndb]
GO
DECLARE@return_value int
EXEC@return_value = [dbo].[sp_GetLikeJobsByJobNumber]
@number = N'23913'
SELECT'Return Value' = @return_value
GO
Am I getting an "Msg 8114, Level 16, State 5, Procedure sp_GetLikeJobsByJobNumber, Line 16
Error converting data type varchar to numeric." error? Line 16 is "@number varchar = null"
View 5 Replies
View Related
Mar 14, 2014
Is it possible to write a stored procedure that will be used registered server?
For example I have registered server named 'Test Servers' and I want to use it in stored procedure
View 3 Replies
View Related
Jun 2, 2014
I have various ways of getting the parameters of a stored procedure:
I have a procedure that has all defaults 4 are null and 2 are 0.
The following shows most of what I need but no defaults
SELECT PARAMETER_NAME ,
ORDINAL_POSITION ,
DATA_TYPE ,
CHARACTER_MAXIMUM_LENGTH ,
CHARACTER_OCTET_LENGTH ,
NUMERIC_PRECISION ,
[Code] ...
This one has two values:
PARAMETER_HASDEFAULT (always 0) and PARAMETER_DEFAULT (always 0)
sp_procedure_params_rowset proc procedure
Is there something else that would tell me if there is a default on a parameter and what the default is if there is one.
View 2 Replies
View Related
Jun 6, 2014
I'm working on building a report and asked a developer which table some data comes from in an application. His answer was the name of a 3500 line stored procedure that returns 2 result sets. I could accomplish what I'm trying to do using the second result set, but I'm not sure how to put that into a temporary table so that I could use it.
Here's my plan according to the Kübler-Ross software development lifecycle:
Denial - Ask the developer to make sure this is correct (done)
Despair - Look hopelessly for a solution (where I am now)
Anger - Chastise developer
Bargaining - See if I can get him to at least swap the order that the resultsets are returned
Acceptance - Tell the users that this can't be done at present.
View 3 Replies
View Related
Sep 11, 2014
We have a stored procedure that makes a decision to pull records from one of two servers.
If one of these servers became unavailable but was no longer queried would the stored procedure still work? Doesn't SQL recompile stored procedures periodically?
Something like this:
If @ServerAIsDown=1
begin
select * from ServerB.dbo.MyTable
end
else
begin
select * from ServerA.dbo.MyTable
end
View 2 Replies
View Related
Mar 11, 2015
Firstly may I say that the sproc I am having problems with and the service that calls it is inherited technical debt from an unsupervised contractor. We are not able to go through a rewriting process at the moment so need to live with this if possible.
Background
We have a service written in c# that is processing packages of xml that contain up to 100 elements of goods consignment data. In amongst that element is an identifier for each consignment. This is nvarchar(22) in our table. I have not observed any IDs that are different in length in the XML element.
The service picks up these packages from MSMQ, extracts the data using XPATH and passes the ID into the SPROC in question. This searches for the ID in one of our tables and returns a bool to the service indicating whether it was found or not. If found then we add a new row to another table. If not found then it ignores and continues processing.
Observations
The service seems to be dealing with a top end of around 10 messages a minute... so a max of about 1000 calls to the SPROC per minute. Multi-threading has been used to process these packages but as I am assured, sprocs are threadsafe. It is completing the calls without issue but intermittently it will return FALSE. For these IDs I am observing that they exist on the table mostly (there are the odd exceptions where they are legitimately missing). e.g Yesterday I was watching the logs and on seeing a message saying that an ID had not been found I checked the database and could see that the ID had been entered a day earlier according to an Entered Timestamp.
So the Sproc...
USE [xxxxxxxxxx]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
[Code]....
So on occasions (about 0.33% of the time) it is failing to get a bit 1 setting in @bFound after the SELECT TOP(1).
The only suggestions I can make have been...
change @pIdentifier nvarchar(25) to nvarchar(22)
Trim any potential blanks from either side of both parts of the identifier comparison
Change the SELECT TOP(1) to an EXISTS
The only other thought is the two way parameter direction in the C# for the result OUTPUT. Not sure why he did it that way or what the purpose is.
I have been unable to replicate this using a test app and our test databases. Has observed selects failing to find even though the data is there, like this before?
View 6 Replies
View Related
Mar 25, 2015
My stored procedure returns one row. I need to insert the result to a table. Can I right something like that:
=============
Insert into table1
exec myProc '1/1/15', 3/1/15'
=====
Or may be I can use Table-valued function insted of myProc?
View 4 Replies
View Related
Apr 29, 2015
I would like to know if it is possible to build one SP to perform all the functions (Add, Update, delete and Select) and then use this in my code instead of making one SP per action. I know this is possible but the update part throws me a little. I used an online example to explain where I fall short on the subject.
USE [SomeTable]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[MasterInsertUpdateDelete]
[Code] ....
So using this as the Stored Procedure, how would I update a records Salary alone by the ID without having to use all the information with just the salary being the new value?
View 8 Replies
View Related
Jun 8, 2015
I have the following Query.
SELECT CAST(DEL_INTERCOMPANYRETURNACTIONID AS NVARCHAR(4000)) COLLATE DATABASE_DEFAULT AS DEL_INTERCOMPANYRETURNACTIONID, 'SRC_AX.PURCHLINE.DEL_INTERCOMPANYRETURNACTIONID' FROM SRC_AX.PURCHLINE WHERE DEL_INTERCOMPANYRETURNACTIONID IS NULL UNION
SELECT CAST(DEL_INTERCOMPANYRETURNACTIONID AS NVARCHAR(4000)) COLLATE DATABASE_DEFAULT AS DEL_INTERCOMPANYRETURNACTIONID, 'SRC_AX.SALESLINE.DEL_INTERCOMPANYRETURNACTIONID'
[Code] .....
My tabel is HST_MASTER.Control.
I want to have this query in a stored procedure. What syntax stored procedure i need to make to fill my table.
View 1 Replies
View Related
Feb 16, 2012
I have a stored procedure that returns XML using FOR XML Explicit. I need to use the output of this procedure in another procedure, and modify the xml output before it is saved somewhere.
Say StoredProc1 is the one returning xml output and StoredProc2 needs to consume the output of StoredProc1
I declared a nvarchar(max) variable and trying to saved the result of StoredProc1
Declare @xml nvarchar(max)
EXEC @xml = StoredProc1 @Id
This doesn't work as expected as @xml doesn't get any value assigned or rather I would say
EXEC @xml = StoredProc1 @Id
outputs the entire xml whereas it should just save the xml in a variable.
View 7 Replies
View Related
Aug 20, 2013
Is it possible to return the results of a stored procedure into either a CTE or temp table?
In other words, is it possible to do this:
with someCTE as (
exec someStoredProc
)
or this:
exec someStoredProc into #tempTable
???
View 9 Replies
View Related
