Help With Stored Proceedure Please

Jul 20, 2005

Hope someone can help.

I am trying to write a stored proceedure to display sales activity by month
and then sum all the columbs.
The problem is that our sales year starts in April and end in March.
So far I have been able to get the sales info my using to sp's, one that say
month >3 and the other says <4. I pass in a year parameter, that for this
years figures would be 2003 for sp1 and 2004 for sp4.
I am sure there is a better way.
Below is a copy of one of my sp's.
Hope you are able to help.

John

ALTER PROCEDURE dbo.sp_SalesAnalFigures_P1
(@Year nvarchar(50),

@CCode varchar(50),

@SCode varchar(50),

@OType varchar(50))

AS SELECT TOP 100 PERCENT DATEPART(mm, dbo.InvoiceHeaderTbl.InvoiceDate) AS
Month, SUM(dbo.InvoiceHeaderTbl.InvoiceTotalNet) AS Sales,

SUM(dbo.InvoiceItemsCostQry.TotalCost) AS Cost,
SUM(dbo.InvoiceHeaderTbl.InvoiceTotalNet -
dbo.InvoiceItemsCostQry.TotalCost) AS Margin,

COUNT(dbo.InvoiceHeaderTbl.InvoiceNo) AS NoOfInvoices,
AVG(dbo.InvoiceHeaderTbl.InvoiceTotalNet) AS AverageValue

FROM dbo.InvoiceHeaderTbl INNER JOIN

dbo.InvoiceItemsCostQry ON dbo.InvoiceHeaderTbl.InvoiceNo =
dbo.InvoiceItemsCostQry.InvoiceNo

WHERE (DATEPART(yyyy, dbo.InvoiceHeaderTbl.InvoiceDate) = @Year) AND
(dbo.InvoiceHeaderTbl.CompanyCode LIKE @CCode) AND

(dbo.InvoiceHeaderTbl.SalesManCode LIKE @SCode) AND
(dbo.InvoiceHeaderTbl.OrderType LIKE @OType)

GROUP BY DATEPART(mm, dbo.InvoiceHeaderTbl.InvoiceDate)

HAVING (DATEPART(mm, dbo.InvoiceHeaderTbl.InvoiceDate) > 3)

ORDER BY DATEPART(mm, dbo.InvoiceHeaderTbl.InvoiceDate)

View 6 Replies


ADVERTISEMENT

Stored Proceedure

Feb 21, 2007

Hi people,
 I'm using the following SP to return the rank of users but I really want it to return just a single Row column and also a Count() of the numer of Users there are....at the moment it sending me the whole table of ranked users.
 Any ideas?
 
ALTER PROCEDURE dbo.RankUsersOnScore
(
@UserID INT
)
AS SET NOCOUNT ON;
SELECT Row, Score, UserID
FROM (SELECT ROW_NUMBER() OVER (ORDER BY Score DESC)
AS Row, Score, UserID FROM dbo.tblUserStats )
AS tblUsersRanked

View 1 Replies View Related

Stored Proceedure

Mar 2, 2004

I was using a proceedure i created in code but because of the way i am using it i decided that a stored proceedure will work better

i have a few add proceedures that work for insert and update but when i tried a select command, no matter what the data entered it returns the first record in the table when I fill the dataset does any one have a clue as to why it would do this
is there something you have to return through the stored proceedure
like you do when you use @@Identity

this is what i currently have,

CREATE PROCEDURE Location_Select


@p1 char(15)
AS
SELECT LocationID FROM t_Location WHERE (LocationPhone = @p1)
go

do i need this? and if so what can i return i tried LocationID and it yelled at me saying that is an invalid column name


CREATE PROCEDURE Location_Select


@p1 char(15),
@retval int output

AS
SELECT LocationID FROM t_Location WHERE (LocationPhone = @p1)

SET @retval =LocationID
GO



thankyou

View 2 Replies View Related

DTS / Stored Proceedure

Apr 16, 2004

How can you run a DST package from a stored proceedure.
I am using sql server 2000
i cant find the syntax anywhere
it is a DTS that takes a file and imports it into a table in the db

View 17 Replies View Related

Increment With Stored Proceedure

Jan 4, 2007

Howdy team,
 How would I increment a field of type 'int' with a stored proceedure with sql2005
Thanks.

View 5 Replies View Related

Sql String In Stored Proceedure

Jan 28, 2004

I've got a stored procedure which contains a fast_forward cursor. I was wondering whether it is possible to pass in an sql query string into this stored procedure for the cursor, i.e:

Create Procedure (@sqlString as text)
as
DECLARE aCursor CURSOR
FAST_FORWARD
FOR @sqlString

View 4 Replies View Related

Stored Proceedure Problem

Mar 8, 2004

CREATE PROCEDURE BatchID_Select

@Que bigint,
@retval varchar(10) OUTPUT

AS

SELECT BatchID FROM t_Que WHERE (QueID = @Que)

SET @retval =BatchID
GO


This stored proceedure will not let me save is there any reason why
if i take out the set line it says the syntax works
if i leave this line in however it says that batchid is not a valid column name
can you only return @@ variables?
i just cant figure out what its problem is

View 2 Replies View Related

Calling One Stored Proceedure From Another

Oct 16, 2007

yet another question unfortunately

I have now created a stored proceedure that has a return parameter, not i am unsure how to call it from another proceedure,

ie
say i have select projectid, project name from projects into temp from projects.

how can i then loop around all the rows in temp, to call my stored proceedure for each record?

in vb i would have created a function like my stored proceedure, then picked up a recordset, looped around it and picked up the return value for each row.

can this be done for sql?

I am trying to do something like

for each record in #temp (projectid, project name) find the stored sprceedure value

so my end result will look like

projectid, project name, @storedproceedure return value
lprojectid, project name, @storedproceedure return value
projectid, project name, @storedproceedure return value
projectid, project name, @storedproceedure return value

any help appreciated

View 1 Replies View Related

View Or Stored Proceedure?

Jul 20, 2005

Hope you can give me some advise.I am wanting to build a databse driven website. I am using Access toconnect to an SQL 2000 server to create tables etc.I am using ASP/ASP.Net to build my site.The question I have is on best method to retrive data. Lets say I havea Table of Products, and one of those fileds is CurrentProduct and itsTrue or False. On my web page I want to retreive and list all productsthat are marked as CurrentProduct True.I could do this as part of the SQL statement in the web page ie"Select * From Products Where CurrentProduct=True", I could create aView that only shows Current Products and use Select * FromCurrentProductsView or I could create a stored prodceedure, that looksvery much like a view.I know a bit about Access and Queries which is why I am using accessto manage Sql, but very liitle if anything about Stored Proceedures.When should I use what? And what are the advantages / dis-advantagesof each approach?Many thanks for any help you are able to provide.

View 1 Replies View Related

Stored Proceedure Question - Looping? Or Is There A Better Way?

Jan 17, 2008

I am writing a bit of code for our intranet using ASP.NET C# and SQL2005. We have a program called Aboutface that is a web based firm directory. We are using the SQL database it has in order to pull data out of it and integrate it into our intranet as we dont really care for its original interface.
I am most interested in setting up relationships.
An attorney only has one secretary that supports them, but a secretary can have many attorneys they support. This is the nature of my problem. The code below is my stored procedure. I am passing in an int which is the ID, and my goal is to generate the  ID of and the name of the person who supports/is supported by. With 1 to 1 relationships, it works fine.. With more than that, it blows up because its finding multuple names (of attorneys being supported by a secretary)
 I was told I might want to use a FOR EACH loop in the SP. I assume I would want to also generate a COUNT variable (to be used in the procedure and to also build the rows of my table outside of it.) and  a NAME variable for each name it finds to populate the table....   but from there I am not sure if I am on the right lines of thinking or where to begin.
Any thoughts/suggestions would be greatly appreciated.
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
 
ALTER PROCEDURE [dbo].[GetAttySectyData] (@id as int)
AS
BEGIN
DECLARE @First as varchar(200)
DECLARE @Middle as varchar(50)
DECLARE @Last as varchar(200)
 SELECT @First = [value] FROM dbo.[text] WHERE efield_id = 10741 AND employee_id = (SELECT DISTINCT s.code as code from tblSectyData s, text t where t.employee_id = s.SectyCode and t.employee_id = @id )
SELECT @Middle = [value] FROM dbo.[text] WHERE efield_id = 10906 AND employee_id = (SELECT DISTINCT s.code as code from tblSectyData s, text t where t.employee_id = s.SectyCode and t.employee_id = @id ) SELECT @Last = [value] FROM dbo.[text] WHERE efield_id =10740 AND employee_id = (SELECT DISTINCT s.code as code from tblSectyData s, text t where t.employee_id = s.SectyCode and t.employee_id = @id )
Select ISNULL(@First, '') + ' ' + ISNULL(@Middle, '') + ' ' + ISNULL(@Last,'') AS FullName
END
 

View 1 Replies View Related

Stored Proceedure Merge Data

Mar 1, 2004

I have some data that I am inputing and if the record already exists i would like to add data to the fields that are not populated ONLY if they are not populated
for example


Already in the table
address= "123 this place"
city= ""
State="MI"
Zip="48462"
FirstName=""
LastName=""

being added
address= "123 this place"
city= "Ontate"
State=""
Zip="48462"
FirstName="Person"
LastName="Guy"


once i find that this record already exists because of the address and zip (which i already have complete)
I would like it to update the City first name and last name in the data that is already in the table. thank you for your help

View 4 Replies View Related

Automatic Execution Of A Stored Proceedure

Jul 23, 2005

How do you set a stored proceedure for automatic execution?--Message posted via http://www.sqlmonster.com

View 1 Replies View Related

Hewlp Needed Creating A Stored Proceedure

Feb 21, 2006

Can someone please help me....I have created a DNN module that works on
the test site but when I upload the module zip to a new site I get an
error on creating my stored proceedure as follows:



StartJob
Begin Sql execution

Info
Executing 01.00.00.SqlDataProvider

StartJob
Start Sql execution: 01.00.00.SqlDataProvider file

Failure
SQL Execution resulted in following Exceptions:
System.Data.SqlClient.SqlException: Line 25: Incorrect syntax near '@Str_Title'.
Line 51: Incorrect syntax near '@Str_Title'. at
System.Data.SqlClient.SqlCommand.ExecuteNonQuery() at
Microsoft.ApplicationBlocks.Data.SqlHelper.ExecuteNonQuery(SqlConnection
connection, CommandType commandType, String commandText, SqlParameter[]
commandParameters) at
Microsoft.ApplicationBlocks.Data.SqlHelper.ExecuteNonQuery(String
connectionString, CommandType commandType, String commandText, SqlParameter[]
commandParameters) at
Microsoft.ApplicationBlocks.Data.SqlHelper.ExecuteNonQuery(String
connectionString, CommandType commandType, String commandText) at
DotNetNuke.Data.SqlDataProvider.ExecuteScript(String Script, Boolean
UseTransactions) CREATE PROCEDURE dbo. ListTAS_Journal @PortalID int, @SortOrder
tinyint = NULL, @Str_Title varchar(100) = '', @Str_Text varchar(100) = '' AS IF
ISNULL(@Str_Title, '') = '' or ISNULL(@Str_Text, '') = '' SELECT [EntryID],
[PortalID], [ModuleID], [Title], [Text], [DateAdded], [DateMod], [Owner],
[Access] FROM TAS_Journal WHERE PortalID = @PortalID AND (Title like
COALESCE('%' @Str_Title '%' ,Title , '') AND Text like COALESCE('%' @Str_Text
'%' ,Text, '')) ORDER BY (CASE WHEN @SortOrder = 1 THEN DateAdded WHEN
@SortOrder = 0 THEN DateMod END) DESC, EntryID DESC else /***Select from either
field ***/ SELECT [EntryID], [PortalID], [ModuleID], [Title], [Text],
[DateAdded], [DateMod], [Owner], [Access] FROM TAS_Journal WHERE PortalID =
@PortalID AND (Title like COALESCE('%' @Str_Title '%' ,Title , '') OR Text like
COALESCE('%' @Str_Text '%' ,Text, '')) ORDER BY (CASE WHEN @SortOrder = 1 THEN
DateAdded WHEN @SortOrder = 0 THEN DateMod END) DESC, EntryID DESC

EndJob
End Sql execution: 01.00.00.SqlDataProvider file


The SP looks like this:

/* -------------------------------------------------------------------------------------
/   ListTAS_Journal
/  ------------------------------------------------------------------------------------- */
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS OFF
GO

CREATE PROCEDURE {databaseOwner}{objectQualifier} ListTAS_Journal
    @PortalID int,
    @SortOrder tinyint = NULL,
    @Str_Title varchar(100) = '',
    @Str_Text varchar(100) = ''
AS

IF ISNULL(@Str_Title, '') = '' or   ISNULL(@Str_Text, '') = ''

SELECT
    [EntryID],
    [PortalID],
    [ModuleID],
    [Title],
    [Text],
    [DateAdded],
    [DateMod],
    [Owner],
    [Access]
FROM
    TAS_Journal
WHERE
    PortalID = @PortalID AND
    (Title like COALESCE('%' + @Str_Title + '%' ,Title , '') AND
    Text like COALESCE('%' + @Str_Text + '%' ,Text, ''))


ORDER BY
    (CASE
    WHEN     @SortOrder = 1 THEN DateAdded
    WHEN     @SortOrder = 0 THEN DateMod
    END) DESC, EntryID DESC
else
/***Select from either field
***/
SELECT
    [EntryID],
    [PortalID],
    [ModuleID],
    [Title],
    [Text],
    [DateAdded],
    [DateMod],
    [Owner],
    [Access]
FROM
    TAS_Journal
WHERE
    PortalID = @PortalID AND
    (Title like COALESCE('%' + @Str_Title + '%' ,Title , '') OR
    Text like COALESCE('%' + @Str_Text + '%' ,Text, ''))


ORDER BY
    (CASE
    WHEN     @SortOrder = 1 THEN DateAdded
    WHEN     @SortOrder = 0 THEN DateMod
    END) DESC, EntryID DESC
GO


SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

This SP works on the test site.
Any help would be creatly apreciated

Mark

View 2 Replies View Related

Create New Date Table With A Stored Proceedure

Sep 21, 2007



Hi,

I have a table from which I need to create a report via MSRS2005, however the data in the table is awful in its construction and I was hoping to be able to use a stored proceedure to create a new table in which I can manupulate the data, but my T-SQL programming skills aren't that clever, so if anyone can offer any advice I'd be most grateful:

In the existing table there are two columns; StartDate and EndDate which is pretty self explanitory - what I would like to do is create a new table with only one date column and if there is more than one day between StartDate and EndDate I would like it to fill in every date in between.

For example, if the StartDate is 01/06/2007 and the EndDate 10/06/2007 I'd like the new table to list dates 01/06/2007 through 10/06/2007 inclusive in one column.

Is this possible? All suggestions welcome.

Thanks in advance,

Paul

View 1 Replies View Related

How To Return Event Description From Stored Proceedure?

Nov 15, 2006



I am using C# to insert the form details and passing event id (numeric) to the same stored procedure in my eror handler and need to retrieve the description from event_db to display in MessageBox..



can the stored proceedure send the text?

View 1 Replies View Related

Stored Proceedure Question, How To Pull An Autogenerate Field Value?

Feb 6, 2008

I have a stored proceedure that is adding a record to a database table. When the record is added using an insert statement, the ID field is autogenerated.
I have a second insert statement that inserts into a second table, however, I want/need? to use that ID field in order to link this additional information to the proper record in the initial table.
 Is there an easy way to do a select or just pull the ID?  I was thinking I could do a select before the final insert using 2 or 3 required fields of which used in a select altogether would be unique, but before I did that, I wanted to see if I was missing a better way. I posted the code below....
 set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
 
ALTER PROCEDURE [dbo].[Add_Employee_Data]
(
@LastName as varchar(100),@FirstName as varchar(100),
@MiddleName as varchar(100),@Position as varchar(100),
@Department as varchar(100),
@DirectDial as varchar(100),
@Ext as varchar(100),
@Fax as varchar(100),
@HomePhone as varchar(100),
@CellPhone as varchar(100),
@Partner as varchar(100),
@TimeKeeper as varchar(100),
@Notary as varchar(100),
@Practice as varchar(100),
@SummerAddress as varchar(250),
@SummerPhone as varchar(100),
@LegalNonLegal as varchar(100),
@Bar as varchar(100),
@OtherEmail as varchar(100),
@HomeAddressComplete as varchar(100),
@HomeAddress as varchar(100),
@HomeCity as varchar(100),
@HomeState as varchar(100),
@HomeZip as varchar(100),
@School as varchar(100),
@Degree as varchar(100),
@Status as varchar(100),
@Floor as varchar(100)) AS
DECLARE @Code as varchar(100)
 
INSERT Into tblMain2(LastName,FirstName,MI,Position,Dept,Extension,DirectDial,[FAX DID],HomePhone,CellularPhone,SpousePartner,
TimeKeeper,Notary,PrGroup,SummerAddress,SummerPhone,LegalNonLegal,Bar,OtherEmail,HomeAddressComplete,HomeAddress,HomeCity,
HomeState,HomeZip,Floor,Active)
Values(@LastName,@FirstName,@MiddleName,@Position,@Department,@Ext,@DirectDial,@Fax,@HomePhone,@CellPhone,@Partner,@TimeKeeper,
@Notary,@Practice,@SummerAddress,@SummerPhone,@LegalNonLegal,@Bar,@OtherEmail,@HomeAddressComplete,@HomeAddress,
@HomeCity,@HomeState,@HomeZip,@Floor,@Status)
<<<<< Put select statement here to pull in @Code where LastName = @LastName  and Extension =@Ext ??
INSERT Into Education(Code,CollegeSchool,DegreeCert) Values(@Code,@School,@Degree)
 
 
 

View 6 Replies View Related

How Do I Get The Returned Id From The Store Proceedure In Asp C#?

Mar 12, 2007

my store proceedure gets the id:
 
 
CREATE PROCEDURE createpost( @userID integer, @categoryID integer, @title varchar(100), @newsdate datetime, @story varchar(250), @wordcount int) asDECLARE @newNewsID integer
Insert Into TB_News(UserID, CategoryID, title, newsdate, StoryText, wordcount)Values (@userID, @categoryID, @title, @newsdate, @story, @wordcount)
SELECT @newNewsID = @@IDENTITY  
 
then im calling it in the asp:
 
  con = new SqlConnection ("server=declt; uid=c1400046; pwd=c1400046; database=c1400046");  con.Open();          cmdselect = new SqlCommand("createpost", con);  cmdselect.CommandType = CommandType.StoredProcedure;  
    cmdselect.Parameters.Add("@userID", userID);  cmdselect.Parameters.Add("@categoryID", categoryID  );   cmdselect.Parameters.Add("@title", title.Text );   cmdselect.Parameters.Add("@newsdate", newsdate.Text );   cmdselect.Parameters.Add("@story", story.Text );    cmdselect.Parameters.Add("@wordcount", "1" );      int valueinserted = cmdselect.ExecuteNonQuery();      Response.Redirect("http://declt/websites/c1400046/newpicture.aspx?id="+valueinserted);     con.Close();
 
as you can see im using the valueinserted but thats just returning 1, but im guessing that means it sucessful. but i want the id of the new record! any idea how ?
 
 

View 2 Replies View Related

Return The New Id From A Store Proceedure

Mar 12, 2007

i have a store procedure which i need to get the returned id from how do i do this??

the sp:
CREATE PROCEDURE createpost(
@userID integer,
@categoryID integer,
@title varchar(100),
@newsdate datetime,
@story varchar(250),
@wordcount int
)
as

Insert Into TB_News(UserID, CategoryID, title, newsdate, StoryText, wordcount)
Values (@userID, @categoryID, @title, @newsdate, @story, @wordcount)

im calling it from asp, maybe i need to do something here as well im a bit lost really.. any help be great!

the asp:
con = new SqlConnection ("server=declt; uid=c1400046; pwd=c1400046; database=c1400046");
con.Open();

cmdselect = new SqlCommand("createpost", con);
cmdselect.CommandType = CommandType.StoredProcedure;



cmdselect.Parameters.Add("@userID", userID);
cmdselect.Parameters.Add("@categoryID", categoryID );
cmdselect.Parameters.Add("@title", title.Text );
cmdselect.Parameters.Add("@newsdate", newsdate.Text );
cmdselect.Parameters.Add("@story", story.Text );
cmdselect.Parameters.Add("@wordcount", "1" );


int userinserted = cmdselect.ExecuteNonQuery();


Response.Redirect("http://declt/websites/c1400046/newpicture.aspx?id=1");

View 1 Replies View Related

Calling A Stored Procedure Inside Another Stored Procedure (or Nested Stored Procedures)

Nov 1, 2007

Hi all - I'm trying to optimized my stored procedures to be a bit easier to maintain, and am sure this is possible, not am very unclear on the syntax to doing this correctly.  For example, I have a simple stored procedure that takes a string as a parameter, and returns its resolved index that corresponds to a record in my database. ie
exec dbo.DeriveStatusID 'Created'
returns an int value as 1
(performed by "SELECT statusID FROM statusList WHERE statusName= 'Created') 
but I also have a second stored procedure that needs to make reference to this procedure first, in order to resolve an id - ie:
exec dbo.AddProduct_Insert 'widget1'
which currently performs:SET @statusID = (SELECT statusID FROM statusList WHERE statusName='Created')INSERT INTO Products (productname, statusID) VALUES (''widget1', @statusID)
I want to simply the insert to perform (in one sproc):
SET @statusID = EXEC deriveStatusID ('Created')INSERT INTO Products (productname, statusID) VALUES (''widget1', @statusID)
This works fine if I call this stored procedure in code first, then pass it to the second stored procedure, but NOT if it is reference in the second stored procedure directly (I end up with an empty value for @statusID in this example).
My actual "Insert" stored procedures are far more complicated, but I am working towards lightening the business logic in my application ( it shouldn't have to pre-vet the data prior to executing a valid insert). 
Hopefully this makes some sense - it doesn't seem right to me that this is impossible, and am fairly sure I'm just missing some simple syntax - can anyone assist?
 

View 1 Replies View Related

How To Search And List All Stored Procs In My Database. I Can Do This For Tables, But Need To Figure Out How To Do It For Stored Procedures

Apr 29, 2008

How do I search for and print all stored procedure names in a particular database? I can use the following query to search and print out all table names in a database. I just need to figure out how to modify the code below to search for stored procedure names. Can anyone help me out?
 SELECT TABLE_SCHEMA + '.' + TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'

View 1 Replies View Related

Execute Stored Procedure Y Asynchronously From Stored Proc X Using SQL Server 2000

Oct 14, 2007

I am calling a stored procedure (say X) and from that stored procedure (i mean X) i want to call another stored procedure (say Y)asynchoronoulsy. Once stored procedure X is completed then i want to return execution to main program. In background, Stored procedure Y will contiue his work. Please let me know how to do that using SQL Server 2000 and ASP.NET 2.

View 3 Replies View Related

Transact SQL :: Can Invoke Stored Procedure Stored Inside From User Defined Table Column?

Nov 5, 2015

Can I invoke stored procedure stored inside from a user defined table column?

View 5 Replies View Related

Using A Stored Procedure To Query Other Stored Procedures And Then Return The Results

Jun 13, 2007

Seems like I'm stealing all the threads here, : But I need to learn :) I have a StoredProcedure that needs to return values that other StoredProcedures return.Rather than have my DataAccess layer access the DB multiple times, I would like to call One stored Procedure, and have that stored procedure call the others to get the information I need. I think this way would be more efficient than accessing the DB  multiple times. One of my SP is:SELECT I.ItemDetailID, I.ItemDetailStatusID, I.ItemDetailTypeID, I.Archived,     I.Expired, I.ExpireDate, I.Deleted, S.Name AS 'StatusName', S.ItemDetailStatusID,    S.InProgress as 'StatusInProgress', S.Color AS 'StatusColor',T.[Name] AS 'TypeName',    T.Prefix, T.Name AS 'ItemDetailTypeName', T.ItemDetailTypeID    FROM [Item].ItemDetails I    INNER JOIN Item.ItemDetailStatus S ON I.ItemDetailStatusID = S.ItemDetailStatusID    INNER JOIN [Item].ItemDetailTypes T ON I.ItemDetailTypeID = T.ItemDetailTypeID However, I already have StoredProcedures that return the exact same data from the ItemDetailStatus table and ItemDetailTypes table.Would it be better to do it above, and have more code to change when a new column/field is added, or more checks, or do something like:(This is not propper SQL) SELECT I.ItemDetailID, I.ItemDetailStatusID, I.ItemDetailTypeID, I.Archived,     I.Expired, I.ExpireDate, I.Deleted, EXEC [Item].ItemDetailStatusInfo I.ItemDetailStatusID, EXEC [Item].ItemDetailTypeInfo I.ItemDetailTypeID    FROM [Item].ItemDetails IOr something like that... Any thoughts? 

View 3 Replies View Related

Using Table Name Stored In A Scalar Variable In Stored Procedure Problem

Mar 27, 2006

Hello to all!

I have a table name stored in a scalar variable (input parameter of my stored procedure). I need to run SQL statement: SELECT COUNT (*) FROM MyTable and store the result of my query in a scalar variable:

For example:

declare @countRows int

set @countRows = (select count(*) from MyTable)

The problem is that the name of MyTable is stored in the input variable of my stored procedure and of corse this does not work:

declare @countRows int

set @countRows = (select count(*) from @myTableName)

I also tried this:

declare @sqlQuery varchar(100)

set @sqlQuery = 'select count(*) from ' + @myTableName

set @countRows = exec(@sqlQuery)

But it looks like function exec() does not return any value...

Any idea how to solve this problem?

Thanx,

Ziga

View 3 Replies View Related

How To Save Stored Procedure To NON System Stored Procedures - Or My Database

May 13, 2008

Greetings:

I have MSSQL 2005. On earlier versions of MSSQL saving a stored procedure wasn't a confusing action. However, every time I try to save my completed stored procedure (parsed successfully ) I'm prompted to save it as a query on the hard drive.

How do I cause the 'Save' action to add the new stored procedure to my database's list of stored procedures?

Thanks!

View 5 Replies View Related

Stored Procedure Being Saved In System Stored Procedures

Apr 7, 2006

We recently upgraded to SQL Server 2005. We had several stored procedures in the master database and, rather than completely rewriting a lot of code, we just recreated these stored procedures in the new master database.

For some reason, some of these stored procedures are getting stored as "System Stored Procedures" rather than just as "Stored Procedures". Queries to sys.Objects and sys.Procedures shows that these procs are being saved with the is_ms_shipped field set to 1, even though they obviously were not shipped with the product.

I can't update the sys.Objects or sys.Procedures views in 2005.

What effect will this flag (is_ms_shipped = 1) have on my stored procedures?

Can I move these out of "System Stored Procedures" and into "Stored Procedures"?

Thanks!

View 24 Replies View Related

How Can I Call One Or More Stored Procedures Into Perticular One Stored Proc ?

Apr 23, 2008

Hello friends......How are you ? I want to ask you all that how can I do the following ?
I want to now that how many ways are there to do this ?



How can I call one or more stored procedures into perticular one Stored Proc ? in MS SQL Server 2000/05.

View 1 Replies View Related

SSIS And Stored Procedures Results Stored In #Tables

Mar 26, 2008

Hello
I'm start to work with SSIS.

We have a lot (many hundreds) of old (SQL Server2000) procedures on SQL 2005.
Most of the Stored Procedures ends with the following commands:


SET @SQLSTRING = 'SELECT * INTO ' + @OutputTableName + ' FROM #RESULTTABLE'

EXEC @RETVAL = sp_executeSQL @SQLSTRING


How can I use SSIS to move the complete #RESULTTABLE to Excel or to a Flat File? (e.g. as a *.csv -File)

I found a way but I think i'ts only a workaround:

1. Write the #Resulttable to DB (changed Prozedure)
2. create data flow task (ole DB Source - Data Conversion - Excel Destination)

Does anyone know a better way to transfer the #RESULTTABLE to Excel or Flat file?

Thanks for an early Answer
Chaepp

View 9 Replies View Related

MS SQL Stored Procedures Inside Another Stored Procedure

Jun 16, 2007

Hi,
 Do you know how to write stored procedures inside another stored procedure in MS SQL.
 
Create procedure spMyProc inputData varchar(50)
AS
 ----- some logical
 
 procedure spMyProc inputInsideData varchar(10)
AS
   --- some logical
  ---  go
-------

View 5 Replies View Related

Calling A Stored Procedure From Within A Stored Proc

Dec 18, 2007

Hi Peeps
I have a SP that returns xml
I have writen another stored proc in which I want to do something like this:Select FieldOne, FieldTwo, ( exec sp_that_returns_xml ( @a, @b) ), FieldThree from TableName
But it seems that I cant call the proc from within a select.
I have also tried
declare @v xml
set @v = exec sp_that_returns_xml ( @a, @b)
But this again doesn't work
I have tried changing the statements syntax i.e. brackets and no brackets etc...,
The only way Ive got it to work is to create a temp table, insert the result from the xml proc into it and then set @v as a select from the temp table -
Which to be frank is god awful way to do it.
 Any and all help appreciated.
Kal

View 3 Replies View Related

Differnce Between Stored Procedure And Stored Functions?

May 22, 2006

Hi all,
What is the Difference between Stored Procedure and Inline Quries...... Is Stored Procedure is faster then Inline Query...
What is the professional Approch....
I m using Inline Quries in my Professional Project with Microsoft Application Block..... Someone Said me this is not a professional approch... use stored procedure instead of Inline Quries... i m so puzzled...
plz guide me..... :)
Thanx
Sajjad

View 3 Replies View Related

How To Stored A Stored Procedure's Results Into A Table

Jul 23, 2005

Hi, How can I store a stored procedure's results(returning dataset) intoa table?Bob*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!

View 1 Replies View Related

Why Is It Called Stored Procedure Instead Of Stored Sets?

Jul 23, 2005

Since RDMBS and its language SQL is set-based would it make more senseto call a given stored process "Stored Sets" instead of currenttheorically misleading Stored Procedure, as a measure to prodprogrammers to think along the line of sets instead of procedure?

View 4 Replies View Related







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