SQL 2012 :: Parsing Variables Between Stored Procedures

Oct 20, 2014

I've got a number of stored procedures that I have for reporting

All are of a similar starting format

For easier maintenance and to take away the need to change all of them if the methodology changes I want to split out shared code.

What I want to do is to take out the part that populates the @ID1 table into a separate stored proc which will be called from the report procs. The values from the shared proc will then be parsed back to the reporting proc.

I thought about using a function but I don't think it will be flexible enough as in certain cases I want to parse 2 or more IDs back into the final output.

I also don't want to make the code too complex so that it is relatively easy to read

CREATE PROC dbo.ReportM1 @ID INT AS
DECLARE
@ID1 TABLE (ID INT PRIMARY KEY, UNIQUE(ID))
IF @ID = 0
INSERT INTO @ID1

[Code] ....

The first question I have is: can i do it with a table variable when going between procs or do i need to build a real table if i want it to maintain the logic in 1 place.

May be worth bearing in mind that the end user who will be executing the proc will only have read + execute stored proc access permissions so dropping, updating or creating real tables is not an option. #Temp tables are possible but since am using table variables throughout would prefer to stick with them.

View 2 Replies


ADVERTISEMENT

SQL Server 2012 :: Finding Procedures That Use Declared Table Variables?

Oct 22, 2014

know a way to find all stored procedures that use declared or temp tables, i.e

Declare @temptable TABLE as....

Create table #temptable

View 8 Replies View Related

Stored Procedures Variables

Dec 28, 2005

I have a PROC that I want to modify via a variable I will pass in from ASP.Net.

Currently the PROC is as follows:

ALTER Procedure [dbo].[spu_FindProperties]
@Search varChar(30)
AS
Set @Search = '%'+ @Search + '%'

SELECT dbo.EDCclient.CLIENT, dbo.EDCclient.CLIENT_ID, dbo.EDCproperty.PROPERTY, dbo.EDCproperty.PROP_ID, dbo.EDCproperty.CLIENT_ID AS Expr1,
dbo.EDCproperty.TRACKER, dbo.EDCproperty.SUBTRACKER, dbo.EDCproperty.PHONE, dbo.EDCproperty.APT_UNITS, dbo.EDCproperty.ESP,
dbo.EDCproperty.CTRLS_INST, dbo.EDCproperty.TODO, dbo.EDCproperty.SWO
FROM dbo.EDCclient INNER JOIN
dbo.EDCproperty ON dbo.EDCclient.CLIENT_ID = dbo.EDCproperty.CLIENT_ID
WHERE (dbo.EDCclient.CLIENT like @Search) OR
(dbo.EDCproperty.PROPERTY like @Search)

I now want to modify it so that the entire Where clause is created in ASp and passed in. So I think my new PROC should look as follows:

ALTER Procedure [dbo].[spu_FindProperties]
@Search varChar(200)
AS

SELECT dbo.EDCclient.CLIENT, dbo.EDCclient.CLIENT_ID, dbo.EDCproperty.PROPERTY, dbo.EDCproperty.PROP_ID, dbo.EDCproperty.CLIENT_ID AS Expr1,
dbo.EDCproperty.TRACKER, dbo.EDCproperty.SUBTRACKER, dbo.EDCproperty.PHONE, dbo.EDCproperty.APT_UNITS, dbo.EDCproperty.ESP,
dbo.EDCproperty.CTRLS_INST, dbo.EDCproperty.TODO, dbo.EDCproperty.SWO
FROM dbo.EDCclient INNER JOIN
dbo.EDCproperty ON dbo.EDCclient.CLIENT_ID = dbo.EDCproperty.CLIENT_ID

@Search)

But I can't get SQL to accept the @Search in place of the Where... Any ideas?

View 7 Replies View Related

Need Help Wioth Variables In Stored Procedures

Mar 15, 2007

I'm working on my first stored procedure, having just watched a video about how to use them.  I have a working procedure that looks like this:set ANSI_NULLS ONset QUOTED_IDENTIFIER ONGOALTER PROCEDURE [dbo].[InMateSearch]    @State nvarchar(255)   AS       BEGIN    DECLARE @SqlString nvarchar(255)       SELECT [FIRST NAME], [LAST NAME], [STATE]     FROM members     WHERE suspend=0 AND (State=@State OR Country=@State)ENDWhat I need to do is build the WHERE clause of the SELECT Statement based on the values of the parameters passed in.  There will be a lot of them, but State will do for an example.I want to assign a variable, say SqlString  = " suspend=0".  Then I want to check the value of @State which is a parameter being passed in, then do something like this:If @State <> "" then SqlString = SqlString & " AND (State=@State OR Country=@State)" so that I'm only filtering by state if the state parameter isn't empty.  Then I would like my SELECT statement to look like: SELECT [FIRST NAME], [LAST NAME], [STATE]  FROM members  WHERE (SqlString)I'm declaring SqlString as the video indicated, but in the video the variable is assigned a value from the database.  I think I need a different kind of variable.  If I try to do something like SqlString = "test" or @SqlString = "test" I get an error.  Once I define or declare a variable I can use to build the string, how do I assign it to the WHERE clause?Diane  

View 3 Replies View Related

Stored Procedures With Output Variables

May 25, 2007

Hi,



What is the syntax calling a stored procedure (let say named 'myproc') with OUTPUT variables (lets say the proc has an integer output variable named 'myvar') ?



Thanks,

Dror.

View 10 Replies View Related

Creating Stored Procedures Based On Variables

Oct 5, 2006

Hi There,

I would like to know if for example i have some tables in my DataBase and i need from each table to get for instance, the Name by the ID, how can i make only one procedure which use parameters and variables so i can use this SP to get the Name By ID for all the Tables?

Thanks

View 1 Replies View Related

Memory Usage For Local Variables In Stored Procedures

Jul 3, 2003

I would like to know if there is a penalty for Varchar variables in stored procedures if I declare them Varchar(8000) instead of Varchar(1000).
I have a lot of variables and sometimes the content will be more them 1000 characters.
Is memory only allocated for the the actual contents or for the complete declared length?

View 2 Replies View Related

SSIS Updating Package Variables From Stored Procedures

Jul 31, 2007


I have been looking at the project Real reference implementation for doing auditing of data uploads. The tables and store procedures are in place using identical field and variable names. However, when running the package it does not update the relevant log entry with the end time and status.

I have performed the process manually running the stored procedures, providing the values directly and everything works fine.

I can only assume that the LogID variable is not being updated during the run and therefore the onEnd procedure cannot update the relevant log entry.

One side effect is that it does update the record when it€™s the first record entered into the table but not on any other inserts. Clearing the table each time is not an option.

This has become rather frustrating and would appreciate any assistance.

Thanks.

View 1 Replies View Related

SQL 2012 :: SSIS Variables Stored On A Database

Jul 3, 2014

I have finally been able to get access to SQL Server 2012, so I am just now getting to play with some of the features. My new company has multiple ways of calling an SSIS package, and some (if not all) use Package Parameters. I just did a little research on these, and I in general get how they work. Here is why I ask...

I liked how my last company had dynamic variables setup. They had all of the required variables stored on a single database table on a single server. This kept all of the connections strings, file paths, file names, etc. that a package would use. My computer, the UAT server, and the Prod server all had the same file in the same location with 1 character difference to note which environment to run in. This way I knew I would always use only the Dev server when I ran a package during development. I would also only touch the test folders with my test data. Once we moved it to UAT or Prod, that single character difference would point to a different set of variables for the same PackageID, and we didn't have to worry about forgetting to change hard coded variable values. The only 2 hard coded variable values were the PackageID and the variable holder database. I have heard of similar ways of doing this via an XML file, but I have not had the (ahem) pleasure of working with that yet.

Jump to my current job and why so far I have not been impressed with the Package Parameters. We have 3 different jobs that all call the same SSIS package. We feed in a different FacilityID, and using values stored in tables we know to look for different file names, etc. To me this seems to be a similar set up to how my last company had set the system. The big difference is my old company used a Script Task to verify which environment we were running in (I can see setting this for different FacilityID's, but that would be cumbersome), and my new company uses multiple tables across multiple databases (I know, this is not the best set up in the world, and we are fixing it soon) to decide what we will use for some of the variable values.

What are your thoughts on storing variables outside a package? Do you have any blog posts or articles you can link to show current best practices?do the Package Parameters only work via the Integrated Services Catalog, or can they be accessed for the File System packages too?

View 2 Replies View Related

SQL 2012 :: User Is Not Able To See Stored Procedures

Jun 12, 2015

user have db_datareader role in in a database.But user is not able to see the stored procedures.

View 5 Replies View Related

Table Names In Stored Procedures As String Variables And Temporary Table Question

Apr 10, 2008

How do I use table names stored in variables in stored procedures?




Code Snippetif (select count(*) from @tablename) = 0 or (select count(*) from @tablename) = 1000000





I receive the error 'must declare table variable '@tablename''

I've looked into table variables and they are not what I would require to accomplish what is needed.
After browsing through the forums I believe I need to use dynamic sql particuarly involving sp_executesql. However, I am pretty new at sql and do not really understand how to use this and receive an output parameter from it(msdn kind of confuses me too). I am tryin got receive an integer count of the records from a certain table which can change to anything depending on what the user requires.




Code Snippet

if exists(Select * from sysobjects where name = @temptablename)
drop table @temptablename




It does not like the 'drop table @temptablename' part here. This probably wouldn't be an issue if I could get temporary tables to work, however when I use temporary tables i get invalid object '#temptable'.

Heres what the stored procedure does.
I duplicate a table that is going to be modified by using 'select into temptable'
I add the records required using 'Insert into temptable(Columns) Select(Columns)f rom TableA'
then I truncate the original table that is being modified and insert the temporary table into the original.

Heres the actual SQL query that produces the temporary table error.




Code Snippet
Select * into #temptableabcd from TableA

Insert into #temptableabcd(ColumnA, ColumnB,Field_01, Field_02)
SELECT ColumnA, ColumnB, Sum(ABC_01) as 'Field_01', Sum(ABC_02) as 'Field_02',
FROM TableB
where ColumnB = 003860
Group By ColumnA, ColumnB

TRUNCATE TABLE TableA

Insert into TableA(ColumnA, ColumnB,Field_01, Field_02)
Select ColumnA, ColumnB, Sum(Field_01) as 'Field_01', Sum('Field_02) as 'Field_02',
From #temptableabcd
Group by ColumnA, ColumnB




The above coding produces

Msg 208, Level 16, State 0, Line 1

Invalid object name '#temptableabcd'.

Why does this seem to work when I use an actual table? With an actual table the SQL runs smoothly, however that creates the table names as a variable problem from above. Is there certain limitation with temporary tables in stored procedures? How would I get the temporary table to work in this case if possible?

Thanks for the help.


View 6 Replies View Related

SQL 2012 :: Stored Procedures / Objects Baseline

Jul 7, 2015

Any method on creating baselines for your stored procedures/objects.

View 4 Replies View Related

SQL 2012 :: How To Hide Stored Procedures From User

Sep 25, 2015

How can I allow a user to run a stored procedure but deny them the ability to see it in SSMS?

I don't mean 'view def' permissions, I mean the actual proc.

I read about encryption which mask the contents but they can still see the proc, any other ways I can accomplish this?

View 9 Replies View Related

SQL Server 2012 :: Finding Unused Stored Procedures

Mar 3, 2014

How do you find stale stored procedures ?

In a scenario where a developer created a slight modification of a stored procedure because he was afraid of breaking something else and took the easy way out, and a few more later down the line, multiple versions of a stored proc. doing slightly different things are just laying around.

"Last used" would be useful piece of information to determine the most recent date a stored procedure was called, either by the application itself or by another stored procedure itself called by the application.

Any stored proc not used for more than say 6 months would then be identified as a candidate for clean-up.

So - short or creating - after the fact - a trigger to update the usage date upon each call - which means a lot of work and no result for the next six months, how can one go about this ?

And could this be done in SS2K8 ?

View 5 Replies View Related

SQL 2012 :: Login / User Cannot Execute Or See Stored Procedures

May 6, 2014

We are using a third party tool that does not store passwords in an encrypted format therefore we created a user with minimal rights. Isn't there a way to grant "execute any stored procedure" to a user/Login? Do we really have to grant execute on each procedure to the user? And then do the same for each new store procedure? The only other option we have found to be able to "see" and execute the stored procedures is by granting "db_owner". I would think that would negate the user being minimal rights.

View 5 Replies View Related

SQL Server 2012 :: Combining 2 Stored Procedures With Different Set Of Parameters

Jun 24, 2015

Is there a way to combine 2 stored procedures with a different set off parameters.

Basically my 1st stored procedure has the following parameters:

1.@PlanID
2.@FinancialYearID
3.@RangetypeID

My second stored proc has the following:

1.@FinancialYearID
2.@IndicatorID
3.@VersionID

I have researched and so far nothing seems to be working. There is a conflict between the FinancialYearID of the 1st and 2nd stored procs.

My overall result is the combination of the 1st and 2nd storedprocs in 1.

View 9 Replies View Related

SQL Server 2012 :: Script To Track When Stored Procedures Changed

Sep 30, 2015

Any script to identify when Stored Procedures were changed?

View 2 Replies View Related

SQL 2012 :: Parameter Error When Executing A Package With Built In Stored Procedures

Jul 23, 2014

I am using Excel VBA to run a stored procedure which executes a package using the built-in SQL Server stored procedures. The VBA passes two values from excel to the stored proc., which is then supposed to pass these "parameters" to the package to use as a variable within the package.

@Cycle sql_variant = 2
WITH EXECUTE AS 'USER_ACCOUNT' - account that signs on using windows authentication
AS
BEGIN
SET NOCOUNT ON;
declare @execution_id bigint

[code]....

When I try to execute the package, from SQL Server or Excel using the Macro I built, I get the following error:"The parameter '[User::Cycle]' does not exist or you do not have sufficient permissions." I have given the USER_ACCOUNT that runs executes the stored procedure permission to read/write to the database and the SSIS project folder.

View 4 Replies View Related

SQL 2012 :: Generate Scripts Result In Order Of Tables And Then Stored Procedures In Server

Sep 10, 2014

I have created one table and one stored procedure for to insert/delete/update the data in that table.

So,I was trying to move the scripts from one database to another by Generating Scripts options in SQL Server.

Generating Scripts:

Object Explorer --> Databases --> Database --> Tasks --> Generate Scripts

The generated script output is in a order of stored procedure first and then table.

REQUIREMENT: My stored procedure is dependent on table. So, I need the table script first and then stored procedure.

Note: I can generate two separate scripts for table and stored procedure, But in a just curiosity to know, Is there any way, can we re order the Generate Scripts output in SQL Server.

View 6 Replies View Related

SQL Server 2012 :: How To Write Stored Procedures To Load Data Model From OLTP To DWH

Nov 24, 2014

How to write Stored Procedures to load the Data Model from OLTP to DWH ?

View 9 Replies View Related

SQL 2012 :: Generate Stored Procedures For Select / Insert / Update / Delete On Certain Tables?

Apr 3, 2015

Is there a way in SQL server that can generate stored procedures for select, insert, update, delete on certain tables?

View 4 Replies View Related

SQL Server 2012 :: Parsing XML That Does Not Quite Fit Normal XML Standards

Jan 21, 2015

I have an application that stores xml data in an unusal manor. Basically a SQL Key column and an XML string.The XML string is not really standard XML, but it is what it is, and I'm stuck with it. It is in the format;

<row key="Value.01" xml:space="preserve"><c1>FirstName</c1><c2>LastName</c2><c3>10 Street Address, City ST 012345-1234</c3><c4>5</c4><c5>50</c5><c6>500</c6></row>

I am able to pull values out via
SELECT
p.value('(./c1)[1]', 'VARCHAR(8000)') AS c1,
p.value('(./c2)[1]', 'VARCHAR(8000)') AS c2
FROM dbo.UserXMLTable
CROSS APPLY XMLRECORD.nodes('/row') t(p)
where p.value('(./c1)[1]', 'VARCHAR(8000)') like 'First%'

However I've been struggling with selecting row with a LIKE clause. Something like ;

SELECT *
FROM dbo.F_UserXMLTable
where XMLRECORD.value('(./c1)[1]', 'VARCHAR(8000)') like 'First%'

I have tried a number of permutations of XML syntax but so far have been stumpled.

Please note "<row key="Value.01" xml:space="preserve">" has a <SP> in the name 'row key' .

View 3 Replies View Related

SQL 2012 :: Parsing Large Varbinary Fields

Jun 9, 2015

I have a table with raw scientific test results in a single field, some of which are over 25Mb field. I need to parse into the field to find and aggregate selected values from the field.

Table structure is
CREATE TABLE [dbo].[Gxxx_Data](
[id] [uniqueidentifier] NOT NULL,
[Status] [nvarchar](50) NULL,
[GxxxItem_ID] [int] NULL,
[Stats_Data] [varbinary](max) NULL,
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

[code]...

From which I need to parse and summarize the (Assembler) opcodes (MOV,CMPi, SHR etc...)I need to parse the large field [Stats_Data] to locate the target data.The internal result strings are delimited with Char(10), conservative counts are from 64k to over 100k lines in each record. Is there a way to parse the individual lines into another table (temp) that would be queried/regexed ?

View 9 Replies View Related

SQL 2012 :: Parsing Out Data String With Text Delimiters

Nov 5, 2014

I'm trying to parse out a line of data that is separated by the text "atc1.", "atc2." etc.

For example,

[atc1.123/atc2.456/atc3.789/atc4.xyz/]

If I only want the data after atc2., then I could search the string for "atc2." and collect all the characters afterwards. But how can I make sure to trim off all the data after "atc3." to make sure I'm only collecting "456" from the example above?

View 2 Replies View Related

Oracle Stored Procedures VERSUS SQL Server Stored Procedures

Jul 23, 2005

I want to know the differences between SQL Server 2000 storedprocedures and oracle stored procedures? Do they have differentsyntax? The concept should be the same that the stored proceduresexecute in the database server with better performance?Please advise good references for Oracle stored procedures also.thanks!!

View 11 Replies View Related

SQL Server 2012 :: Stored Procedures Compiles Even When There Is No CREATE TABLE For A Temp Table

Feb 11, 2015

i am inserting something into the temp table even without creating it before. But this does not give any compilation error. Only when I want to execute the stored procedure I get the error message that there is an invalid temp table. Should this not result in a compilation error rather during the execution time.?

--create the procedure and insert into the temp table without creating it.
--no compilation error.
CREATE PROC testTemp
AS
BEGIN
INSERT INTO #tmp(dt)
SELECT GETDATE()
END

only on calling the proc does this give an execution error

View 3 Replies View Related

Parsing A Sentence In A Stored Procedure

Jul 22, 2000

I have to parse a sentence into separate words. This has to be done in a Stored Proc.

If the sentence is: "The sun is rising". Then I want to store the words "The" "sun" "is" "rising" into 4 separate variables. (The separator is a space).

Has someone already written something like this before? I do not want to re-invent the wheel.

Thankx.

KP

View 1 Replies View Related

Reverse Parsing Stored Procedure

Jul 12, 2006

Hi All,

I wanted to make a .NET wrapper class that parses a database for a given stored procedure, and finds the name and type of each of it's parameters.

to do so, i need to do the following:

normally, you have a stored procedure and you know its parameters and their types (i.e. int, varchar, etc), based on that, you hard code a call for this storedprocedure on your backend code.

I want to make a wrapper class that goes and finds out what the parameters for an existing stored procedure are, and based on them, create a GUI that allows people to fill them. after people fill this Form out, and click submit, i generate the proper procedure call.

this would basically allow me to create a single class that i can just call to create a form for any given storedprocedure...



thanks
Cheers!

View 4 Replies View Related

Parsing The Output Parameter Of A Stored Procedure

Apr 29, 2008

 Hi,
I am trying to cast the parameter returned from the stored procedure to an int32 type. But it is giving me  an error message as " Input string is not in the correct format " 
my code is
int valid  = Int32.Parse(command.Parameters["@Valid"].Value.ToString());
the @Valid parameter is an output parameter and is declared as int in the stored procedure....  the command is a DbCommand object
does anyone have an idea why this error is happening.
Naveen

View 2 Replies View Related

Parsing Character Strings In A Stored Procedure

Jul 11, 2001

Hi guys,

I'm trying to parse a character string in a stored procedure but I'm not sure how to do it. For example:

-------------------------------
create procedure sp_test (
@fld varchar(1000)
)
AS

???
???
.
.
.
.
----------------------------------

sp_test '123:abc,456:def,789:ghi'


I need to put 123 into a seperate local variable, abc into a local variable, and so on...

What would be the best function to use?

Any help would greatly be appreciated.

- Gary

View 1 Replies View Related

Stored Procedures 2005 Vs Stored Procedures 2000

Sep 30, 2006

Hi,



This Might be a really simple thing, however we have just installed SQL server 2005 on a new server, and are having difficulties with the set up of the Store Procedures. Every time we try to modify an existing stored procedure it attempts to save it as an SQL file, unlike in 2000 where it saved it as part of the database itself.



Thank you in advance for any help on this matter



View 1 Replies View Related

Reporting Services :: Expression For Parsing Out Values From XML Stored In Column

Nov 25, 2015

I'm new to SSRS (We are using SQL 2014) and have been asked to create a report from SharePoint list data. One of the columns in my list contains XML data. I need to pull three different fields from this XML. I see lots on using xml as a datasource but I'm using a SharePoint list as my datasource. Before I get too far down the wrong road I thought I'd ask here if there was a built in means of parsing XML or if I should look at using the Text Function under Common Functions in the Epression builder?

View 8 Replies View Related

All My Stored Procedures Are Getting Created As System Procedures!

Nov 6, 2007



Using SQL 2005, SP2. All of a sudden, whenever I create any stored procedures in the master database, they get created as system stored procedures. Doesn't matter what I name them, and what they do.

For example, even this simple little guy:

CREATE PROCEDURE BOB

AS

PRINT 'BOB'

GO

Gets created as a system stored procedure.

Any ideas what would cause that and/or how to fix it?

Thanks,
Jason

View 16 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved