Column Count From Stored Procedure

Jul 20, 2005

I am trying to determine the number of columns that are returned from
a stored procedure using TSQL. I have a situation where users will be
creating their own procedures of which I need to call and place those
results in a temp table. I will not be able to modify those users
procedures. I figure if I have the number of columns I can dynamically
create a temp table with the same number of columns, at which point I
can then perform an INSERT INTO #TempTableCreatedDynamically EXEC
@UserProcCalled. With that said, does anyone have any idea how to
determine the number of rows that an SP will return in TSQL?

Thanks!

View 2 Replies


ADVERTISEMENT

Sql Count Using Stored Procedure Withing Stored Procedure

Apr 29, 2008

I have a stored procedure that among other things needs to get a total of hours worked. These hours are totaled by another stored procedure already. I would like to call the totaling stored procedure once for each user which required a loop sort of thing
for each user name in a temporary table (already done)
total = result from execute totaling stored procedure
Can you help with this
Thanks

View 10 Replies View Related

Using Count(*) In A Stored Procedure

Jul 6, 2007

Hi,
I would like to add custom paging to my ASP.Net pages. How can I use Count(*) in a query such as below to retrieve a record count? 
set ANSI_NULLS ONset QUOTED_IDENTIFIER ONGO
ALTER PROCEDURE [dbo].[view_icon_photos]    @Filenumber int = '0' , @Location nvarchar(50)= '-1' , @Rubric nvarchar(MAX)= '-1' , @Author nvarchar(75)= '-1' , @startRowIndex int= '-1' , @maximumRows int= '0'
As
IF @Filenumber = '0' AND @location > '-1' AND @Rubric = '-1' AND @Author = '-1' AND @startRowIndex > '-1' AND @maximumRows >'0'Select Icon, Filenumber, RowRankFrom(Select Icon, Filenumber,  Row_Number () Over ( Order By Filenumber ASC ) AS RowRankFrom dbo.photosWhere Location = @Location)As IconRank Where RowRank > @startRowIndex AND RowRank <= (@startRowIndex + @maximumRows)
 

View 2 Replies View Related

How Can I Use COUNT In My Stored Procedure?

Jul 29, 2007

hello,
i have a list of Categories and each one contains a list of SubCategories, and i use a nested Repeater to show each Category with their SubCategories, but because are to many to show verticaly, i want to make 2 nested repeaters and in the first one to show only the first "n" Categories with their SubCategories and in the second Repeater should be the last n Categories, and like so they will be in 2 colums
How can i use the COUNT function in my stored procedure to take only first n Categories?
I used SELECT * FROM Categories WHERE CategoryID <= 5 but i don't want to order by the primary key i want to order by a COUNT or something like this...
here is my stored procedure (i use it for two nested Repeaters)ALTER PROCEDURE SubCategoriiInCategorii
CategoryID int)
AS
SELECT CategoryID, Name, Description FROM Categories WHERE CategoryID = @CategoryIDORDER BY Name
 
SELECT p.SubCategoryID, p.Name,p.CategoryID FROM SubCategorii p
ORDER BY p.Name
i hope you understand what i mean, thank you

View 4 Replies View Related

Using Two COUNT(*) In A Stored Procedure

Apr 19, 2005

what's wrong with the following code? COUNT for completed and exempted doesn't return correct values. they return same values. but when i remove one count it's returning right COUNT. i need to return the COUNT from two tables with a single query. how can i accomplish that??? is there anything wrong with my code below?
<code>
CREATE PROCEDURE dbo.Test AsSELECT s.StudentIdNum,  c.[Name], COUNT(*) AS Completed, COUNT(*) AS exempted FROM StudentEnrolled AS s JOIN StudentCourse AS sc ON s.StudentKey = sc.StudentKey JOIN Courses AS c ON sc.CourseID = c.CourseIDLEFT JOIN ModuleEnrolment AS m ON sc.StudentCourseID = m.StudentCourseIDLEFT JOIN ModuleExemption AS e ON sc.StudentCourseID = e.StudentCourseIDWHERE m.Status = "Completed"GROUP BY s.StudentIdNum, c.[Name]
</code>

View 5 Replies View Related

TSQL Stored Procedure - Get Count Where....

Feb 23, 2007

I have the following for sql server 2000...
  Select b.courseName, a.courseId, count(a.courseId) as [count],  avg(convert(INT, a.fldScore)) as [average], count(fldPass) as [passed], count(fldPass) as [failed] From tblTest a  inner join tblTest2 b on a.courseId = b.courseId Group by a.courseId, b.courseName
Problem is the [passed] and [failed]
As it is, it's counting all of them.
I need to adjust it so passed will only read where fldPass = 'yes'
and fldPass = 'no' for the passed and failed.
Suggestions?
Thanks,
Zath

View 4 Replies View Related

Dynamic Sql For Count In Stored Procedure

Apr 29, 2008

Hi all,
I'm using sql 2005. Can some one please tell me how to write dynamic sql for count. What i want is that i want to count the number of employees existing for the given department names and get the counted values as output into my vb.net 2.0 project.  If any one know who to write this please send the code.. Please help me.. I want the below code to change to dynamic sql:
 Alter proc GetCountforemp
@DestName varchar(200)=null,
@total int output
as
begin
SELECT @total = Count(distinct Employee.EmployeeID) FROM Employee
INNER JOIN Dest R on R.DestID=Employee.DestID
WHERE R.DestName in ('''+@DestName+''')
end 

View 1 Replies View Related

Stored Procedure For Each ProductID Count

Aug 5, 2004

Would like to get a total count from Quantity Column for (Each individual ProductID # entered) from the ProductID Column. And insert (the New total Value count into the QtySold Column.

Does anyone have an idea how to write a stored Procedure for this?

The tables name is OrderItems which contains the following columns:

uid, OrderID, ProductID, Quantity, QtySold, ProductName, Price,

Many Thanks

View 5 Replies View Related

Sum Or Count Value Of Field In Stored Procedure

Apr 19, 2005

How can i create a stored procedure that count or sum value of field
e.g.

               
f1      f2    
f3    f4    f5
 record     1      
1      2     
3     1
 
and get answer  like this   1=4  - 2=1 -  3=1

 

View 1 Replies View Related

Stored Procedure - Return Count

Jun 16, 2005

Having a little trouble with this sp... just need to return the count.....CREATE  PROCEDURE dbo.getTotalObjectives @courseId   VARCHAR(20) ASBEGIN DECLARE @errCode     INT
  SELECT count(*)  FROM   objstructure  WHERE  courseId  = @courseId
 SET @errCode = 0  RETURN @errCode Can only return one thing, @errCode, but can return others in the select statement....I did it before like.....SELECT @lessonLocation = lessonLocation  FROM   cmiDataModel  WHERE  studentId = @studentIdand got the lessonLocationAnd, in code behind, I know this may be off as well....sObjNum = command.Parameters[ "@courseId" ].Value.ToString();The @courseId should be the count????Thanks all,Zath

View 4 Replies View Related

Calling A Stored Procedure Which Uses A Return @count

Aug 21, 2007

Hi again, and so soon...Having just solved my previous issue, I am now trying to call this stored proc from my page.aspx.vb code. Firstly the stored proc looks like this:-----------  ALTER PROCEDURE dbo.CountTEstAS   SET NOCOUNT ON    DECLARE @sql nvarchar(100);    DECLARE @recCount int;      DECLARE @parms nvarchar(100); SET @sql = 'SELECT @recCount1 = COUNT(*)            FROM Pool 'SET @parms = '@recCount1 int OUTPUT'             exec sp_executesql @sql, @parms, @recCount1 = @recCount OUTPUTRETURN @recCount -------------When tested from the stored proc, the result is: @RETURN_VALUE = 4  My code that calls this stored proc is:         Dim connect As New SqlConnection("Data Source=.SQLEXPRESS;AttachDbFilename=|DataDirectory|Database.mdf;Integrated Security=True;User Instance=True")        connect.Open()        Dim cmd As New SqlCommand("CountTEst", connect)        cmd.CommandType = CommandType.StoredProcedure        Dim MyCount As Int32        MyCount = cmd.ExecuteScalar        connect.Close() So I expext MyCount = 4 but this code returns MyCount = 0 With the RETURN statement in the stored proc, and  then calling it via MyCount = cmd.ExecuteScalar, I didn't think I need to explicitly define any other parameters. Any help please. thanks, 

View 5 Replies View Related

SQL Problem Involving Count(*) In Stored Procedure

Jun 4, 2008

I seem to have a little problem with my SQL.  I'm attempting to use a an .NET grid-view to page a list of search results using an ObjectDataSource (ODS).  It's a very simple search setup.  The ODS retrieves the Search keyword from the query-string sending it to the BLL's GetResult method which then utilises the DAL's version and retrieves the result.  Not all of them of course because I'm paging so it only retrieves the first page (Lets say the first 10 items).  This part of the SQL works fine as the same SQL method is used else where on the site and it's well tested.  But to to use the Paging function I'm using the SelectCountMethod attribute of the ODS.  Again this gets the Search term from the QueryString and sends it too the BLL which retrieves the information from the DAL.  Now this is where it starts playing up.  The Sql count method is as follows and is run as a Stored Procedure: 1 ALTER PROCEDURE dbo.StoreName_Store_GetProductSearchCount
2 (
3 @SearchQuery nvarchar
4 )
5 AS
6 SET NOCOUNT ON
7
8 SELECT COUNT(*)
9 FROM StoreName_Products
10 WHERE Title LIKE '%' + @SearchQuery + '%'
Nice and Easy.  Not a Problem... Isn't it? When I run this query by selecting the database in the Server Explorer in VS2005  and running a 'New Query', it retrieves the correct results.  (I only run this query from the Start of the SELECT Query in this mode, don't add the stuff above it)When I run this query through the website as intended and retrieves the int from the Stored Procedure, it always returns the entire count of the number of products in the table.At first I thought It might be a simple error with my caching system.  Nope, it's not as after I purged it, it still returns the same value. I thought, well maybe just maybe the BLL or the DAL are some point converting it to this number but I'm quite sure it wasn't... and nope it wasn't that either because the ExecuteScalar method was found to be returning this number.  So I ran the Stored Procedure by opening it up and simply right clicking the SQL Query and Executing it adding a value for @SearchQuery when it requested.  Bingo.  It's returning the full number of the products.So my question is... What's the deal?  How can the same query return two completely different results?  and most of all... How do I overcome this?  Is this because it's in a stored procedure and I should be executing it as a SqlCommand from my DAL?  I'm a bit confused.  Is there a problem running this sort of query in a stored procedure that I don't know about and probably really should?Thankful for any answers,Regards,Luke 

View 11 Replies View Related

Count Records When RecordSource Is A Stored Procedure

Jul 20, 2005

I have a stored procedure named mySP that looks basically like this:Select Field1, Field2From tblMyTableWhere Field 3 = 'xyz'What I do is to populate an Access form:DoCmd.Openform "frmMyFormName"Forms!myFormName.RecordSource = "mySP"What I want to do in VBA is to open frmContinuous(a datasheet form) ifmySP returns more than one record or open frmDetail if mySP returnsonly one record.I'm stumped as to how to accomplish this, without running mySP twice:once to count it and once to use it as a recordsource.Thanks,lq

View 1 Replies View Related

Running Total Count In Stored Procedure

Jul 20, 2005

in my procedure, I want to count the number of rows that have erroredduring an insert statement - each row is evaluated using a cursor, soI am processing one row at a time for the insert. My total count tobe displayed is inside the cursor, but after the last fetch is called.Wouldn't this display the last count? The problem is that the count isalways 1. Can anyone help?here is my code,.... cursor fetchbegin ... cursorif error then:beginINSERT INTO US_ACCT_ERRORS(ERROR_NUMBER, ERROR_DESC, cUSTOMERNUMBER,CUSTOMERNAME, ADDRESS1, ADDRESS2, CITY,STATE, POSTALCODE, CONTACT, PHONE, SALESREPCODE,PRICELEVEL, TERMSCODE, DISCPERCENT, TAXCODE,USERCOMMENT, CURRENCY, EMAILADDRESS, CUSTOMERGROUP,CUSTINDICATOR, DT_LOADED)VALUES(@ERRORNUM, @ERRORDESC,@CUSTOMERNUMBER, @CUSTOMERNAME, @ADDRESS1, @ADDRESS2, @CITY,@STATE, @POSTALCODE, @CONTACT, @PHONE, @SALESREPCODE,@PRICELEVEL, @TERMSCODE, @DISCPERCENT, @TAXCODE,@USERCOMMENT, @CURRENCY, @EMAILADDRESS, @CUSTOMERGROUP,@CUSTINDICATOR, @DTLOADED)SET @ERRORCNT = @ERRORCNT + 1END --error--FETCH NEXT FROM CERNO_US INTO@CUSTOMERNUMBER, @CUSTOMERNAME, @ADDRESS1, @ADDRESS2, @CITY, @STATE,@POSTALCODE, @CONTACT,@PHONE,@SALESREPCODE, @PRICELEVEL,@TERMSCODE,@DISCPERCENT, @TAXCODE, @USERCOMMENT, @CURRENCY,@EMAILADDRESS,@CUSTOMERGROUP, @CUSTINDICATOR, @DTLOADED--IF @ERRORCNT > 0INSERT INTO PROCEDURE_RESULTS(PROCEDURE_NAME, TABLE_NAME, ROW_COUNT,STATUS)VALUES('LOAD_ACCOUNTS', 'LOAD_ERNO_US_ACCT', @ERRORCNT, 'FAILEDINSERT/UPDATE')END -- cursorCLOSE CERNO_USDEALLOCATE CERNO_US

View 1 Replies View Related

Retrieve Count From Stored Procedure And Display In Datagrid.

Feb 9, 2006

Hi Guys,
I have a sql procedure that returns the following result when I execute it in query builder:
CountE   ProjStatus
6            In Progress
3            Complete
4            On Hold
The stored procedure is as follow:
SELECT     COUNT(*) AS countE, ProjStatusFROM         PROJ_ProjectsGROUP BY ProjStatus
This is the result I want but when I try to output the result on my asp.net page I get the following error:
DataBinder.Eval: 'System.Data.DataRowView' does not contain a property with the name Count.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.Web.HttpException: DataBinder.Eval: 'System.Data.DataRowView' does not contain a property with the name Count.Source Error:



Line 271: </asp:TemplateColumn>
Line 272: <asp:TemplateColumn>
Line 273: <itemtemplate> <%# DataBinder.Eval(Container.DataItem, "Count" )%> </itemtemplate>
Line 274: </asp:TemplateColumn>
Line 275: </columns>
My asp.net page is as follows:
<script runat="server">
Dim myCommandPS As New SqlCommand("PROJ_GetProjStatus")

' Mark the Command as a SPROC
myCommandPS.CommandType = CommandType.StoredProcedure
Dim num as integer
num = CInt(myCommand.ExecuteScalar)
'Set the datagrid's datasource to the DataSet and databind
Dim myAdapterPS As New SqlDataAdapter(myCommandPS)
Dim dsPS As New DataSet()
myAdapter.Fill(dsPS)

dgProjSumm.DataSource = dsPS
dgProjSumm.DataBind()

myConnection.Close()
</script>
 
<asp:datagrid id="dgProjSumm" runat="server"

BorderWidth="0"
Cellpadding="4"
Cellspacing="0"
Width="100%"
Font-Names="Verdana,Arial,Helvetica; font-size: xx-small"
Font-Size="xx-small"
AutoGenerateColumns="false">

<columns>
<asp:TemplateColumn HeaderText="Project Summary" HeaderStyle-Font-Bold="true" >
<itemtemplate> <%# BgColor(DataBinder.Eval(Container.DataItem, "ProjStatus" ))%> </itemtemplate>
</asp:TemplateColumn>
<asp:TemplateColumn>
<itemtemplate> <%# DataBinder.Eval(Container.DataItem, "Count" )%> </itemtemplate>
</asp:TemplateColumn>
</columns>
</asp:DataGrid>
Please help if you can Im havin real trouble here.
Cheers
 

View 3 Replies View Related

Row Count For Any Stored Procedure - OPENQUERY No Longer Works

Mar 12, 2015

I have a process that keeps check on the row counts of about 100 stored procedures. The input parameters and "certified" row counts for all of the stored procedures are stored in a database. The process runs every day and executes all of the stored procedures using the parameters from the database with syntax below. The row count returned is compared against the known "certified" row count. If the counts are different, we receive an email alerting us that something has changed with the data or the sp query.

(This code is dynamically generated for all 100 + stored procedures)

SELECT COUNT(*) FROM OPENQUERY(SQLSERVER,'EXEC 'usp_HR_My_Stored_Procedure @inputparam1="12345",@inputparam2="12345"')

This worked well until I upgraded from SQL Server 2008 R2 to SQL Server 2014. Evidently Microsoft fixed this for me. The error below is now received anytime we attempt to execute a stored procedure with dynamic SQL through OPENQUERY.

The metadata could not be determined because statement 'EXEC (@sql_str)' in procedure 'usp_HR_My_Stored_Procedure ' contains dynamic SQL. Consider using the WITH RESULT SETS clause to explicitly describe the result set.

The stored procedures that are monitored change frequently, so it isn't reasonable to create tables with fixed column structures for all for all of the stored procs.

View 5 Replies View Related

Transact SQL :: Stored Procedure For Total Count For New Users

Jul 14, 2015

I need to create a stored procedure for total count of the user's. If  User from front end  invites other user to use my tool, that user will be stored into a table name called "test",lets say it will be stored as"Invited 1 User(s)" or if he invites 2 users it will store into table as "Invited 2 User(s)."

But now we have changed the concept to get the ISID (name of the user)  and now when ever the user invites from the front end, the user who have invited should stored in two tables "test" and " test1" table .

After we get the data into test and test1 table i need the total count of a particular user from both tables test and test1.

if i invite a user , the name of the user is getting stored in both test and test1 tables.Now i want to get the count of a user from both tables which should be 1,but its showing 2.

Reason: Why i am considering the count from 2 tables is because before we were not tracking the usernames and we were storing the count in single test table.,but now we are tracking user names and storing them in both tables(test and test1).

Here is my sample  code:

I need to sum it up to get the total user's from both the table but I should get 1 instead of 2 

SELECT
(select distinct COUNT(*) from dbo.test
where New_Values like  '%invited%'
and Created_By= 'sam'
+
(select distinct count (*)  from dbo.test1
where invited_by ='sam'

View 8 Replies View Related

Stored Procedure To Count Matching Words Between Two Strings

Jan 19, 2007

hi all,

I want to know that how can i write a stored procedure that takes an input param, i.e., a string and returns the count of the matching words between the input parameter and the value of a column in a table. e.g. I have a table Person with a column address. now my stored procedure matches and counts the number of words that are common between the address of the person and the input param string. I am looking forward for any help in this matter. I am using Sql server 2005.

View 1 Replies View Related

SQL Server 2012 :: Can't Get Row Count To Return To Calling Stored Procedure

Jul 9, 2014

SQL Server 2012 Standard SP 1.

Stored procedure A calls another stored procedure B. Rowcount is set properly in called procedure B, but does not seem to return it to calling procedure A. Otherwise the two stored procedures are working correctly. Here is the relevant code from the calling procedure A:

declare @NumBufferManagerRows int = 0;
exec persist.LoadBufferManager @StartTicks, @EndTicks, @TimeDiff, @NumBufferManagerRows;
print 'BufferManagerRows';
print @NumBufferManagerRows;

Print statement prints @NumBufferManagerRows as 0.

Here is the called stored procedure B:

CREATE PROCEDURE [persist].[LoadBufferManager]
-- Add the parameters for the stored procedure here
@StartTicks bigint,
@EndTicks bigint,
@TimeDiff decimal(9,2),
@NumRows int OUTPUT

[Code] ...

View 2 Replies View Related

Integration Services :: Log Inserted And Updated Count Done By Stored Procedure

Sep 8, 2015

I am run a stored procedure using Execute SQL task in, I want to log information of number of record inserted update in my table. I want to enable SSIS logging after from where I get information number of record inserted update.

I am using SQL server 2008R2 standard edition.

View 4 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

Passing Column Name To Stored Procedure

Jan 25, 2008

HII m trying to use the following Store Procedure to search Suppliers... user provides a Column Name (Search By) and an Expression... but i m not getting it work properly... plz review it and tell me wots wrong with this stored procedure is... CREATE PROCEDURE dbo.SearchSupplier    (    @Column  nvarchar(50),    @Expression nvarchar(50)        )    AS            SELECT SupplierID,Name,Address,Country,City,Phone,Fax    FROM Supplier        WHERE @Column LIKE '%'+@Expression +'%' OR @Column = @Expression       RETURN   Thanks

View 2 Replies View Related

Stored Procedure For Updating Bit Datatype Column

Mar 25, 2008

Hi guys,
I have a table with following columns and records.
Empid       Empname        Phone     Flag
14             Rajan                 2143          116             Balan                 4321          122             Nalini                 3456          023             Ganesh              9543          0
Now i need to create a stored procedure which will convert the flag values to vice versa since it is a bit datatype. That is if execute the stored procedure it should convert all the flag values to 1 if it zero and zero's to 1. How? Pls provide me the full coding for the stored procedure.
Thanx. 

View 2 Replies View Related

Passing A Selected Row Column Value To The Stored Procedure

Feb 17, 2006

I have a simple Gridview control that has a delete command link on it.If I use the delete SQL code in line it works fine.  If I use a stored procedure to perform the SQL work, I can't determine how to pass the identity value to the SP.   Snippets are below...The grid<asp:GridView ID="GridView2" runat="server" AllowPaging="True" AllowSorting="True"                    AutoGenerateColumns="False" DataSourceID="SqlDataSource2">                    <Columns>                        <asp:BoundField DataField="member_id" HeaderText="member_id" InsertVisible="False"                            ReadOnly="True" SortExpression="member_id" />                        <asp:BoundField DataField="member_username" HeaderText="member_username" SortExpression="member_username" />                        <asp:BoundField DataField="member_firstname" HeaderText="member_firstname" SortExpression="member_firstname" />                        <asp:BoundField DataField="member_lastname" HeaderText="member_lastname" SortExpression="member_lastname" />                        <asp:BoundField DataField="member_state" HeaderText="State" SortExpression="member_state" />                        <asp:CommandField ShowEditButton="True" />                        <asp:CommandField ShowDeleteButton="True" />                    </Columns>                </asp:GridView>                <asp:SqlDataSource ID="SqlDataSource2" runat="server" ConnectionString="<%$ ConnectionStrings:rentalConnectionString1 %>"                    SelectCommand="renMemberSelect" SelectCommandType="StoredProcedure"                    DeleteCommand="renMemberDelete"  DeleteCommandType="StoredProcedure"                      OldValuesParameterFormatString="original_{0}"                     >                                         <DeleteParameters>                                             <asp:Parameter  Name="member_id" Type="Int32"  />                                              </DeleteParameters>                                            </asp:SqlDataSource>the SPCREATE PROCEDURE renMemberDelete@member_id  as intAs UPDATE [renMembers] SET member_status=1 WHERE [member_id] = @member_idGO

View 1 Replies View Related

Dynamic Column Selection In Stored Procedure

Feb 10, 2005

Hey Guys,

Here is the issue I'm having. I am writing a stored procedure that takes a couple of parameters. Each one is the value within a specific column in one table (i.e., @part = 'o-ring' or @sub_assembly = 'hydraulic ram'). Needless to say, the columns form a hierarchy. What I am trying to achieve is to allow the user to specify one of the parameters and get a count for all records where the specified value is in the corresponding column. So, if the user puts in the parameter @part = 'o-ring', I want it to know that the where clause for the select statement should look for o-ring in the part column and not the sub_assembly column. Here is what I am trying to do, which isn't working.

DECLARE @querycolumn varchar(20),
@queryvalue varchar(35)

SET @querycolumn = ''
SET @queryvalue = ''

IF(@sub_assembly = NULL)
BEGIN
IF(@part = NULL)
BEGIN
PRINT 'This is an error. You must have at least a part'
END
ELSE
BEGIN
SET @querycolumn = 'Part'
SET @queryvalue = @part
END
END
ELSE
BEGIN
SET @querycolumn = 'SubAssembly'
SET @queryvalue = @sub_assembly
END

SELECT SubAssembly, Part, COUNT(RecordID)
FROM Table
WHERE @querycolumn = @queryvalue
GROUP BY SubAssembly, Part
ORDER BY SubAssembly, Part

The problem is that I'm getting an error when I try to use @querycolumn to supply the column name to the WHERE clause. Any ideas or suggestions?

View 14 Replies View Related

Invalid Column Name Error In Stored Procedure

Sep 19, 2012

I have a stored procedure that I am using to convert tables to a new format for a project. The project requires new tables, new fields in existing tables, dropping fields in existing tables and dropping an existing table. The SP takes care of doing all this and copying the data from the tables that are going to be dropped to the correct places. Everything is working fine except for one table and I can't figure out why.

For this particular table, it already exists in the database and has new fields added to it. Then I try and update those fields with values from another table. This is where I am getting the Invalid column name error (line is highlighted in red). If I comment out the code where the error is occurring and run the update alone everything works fine so I know the Update statement works.

Here is the specific error message I am getting in SQL Server 2005:

Msg 207, Level 16, State 1, Line 85
Invalid column name 'AssignedAgent'.
Msg 207, Level 16, State 1, Line 85
Invalid column name 'DateTimeAssigned'.

Here is the SP: -

IF OBJECT_ID('ConvertProofTables','P') IS NOT NULL
DROP PROCEDURE ConvertProofTables;
GO
CREATE PROCEDURE ConvertProofTables
AS
SET ANSI_NULLS ON

[Code] ....

View 7 Replies View Related

Using A Stored Procedure Parameter To Access A Column

Mar 27, 2004

I trying to create a general stored procedure which updates 1 out of 140 columns depending on the column name provided as a parameter.
I'm not having much luck, just wondering if anyone else had tried to do this and whether it is actually possible?
Any help would be much appreciated

Chris

View 4 Replies View Related

Identity Column Set Not For Replication And A Stored Procedure

Feb 2, 2015

I have a table, which is being replicated with the identity column set "not for replication" and a stored procedure, which is also replicated (execution) which inserts into this table. When the execution of the stored procedure happens, the replication monitor complains about identity value not being provided.other than removing the stored procedure from replication?

View 0 Replies View Related

Seek Table Column In Stored Procedure

Dec 13, 2007

Hi All,

The script below may be use to find out what stored procedure uses a specified column from any of the table. This could be helpful in cases you have change a field name of a table and want to find out what stored procedure uses that column.

create procedure seek_sp_for_columns
@colname_para varchar(500)

as

begin
create table #temp_t
(
textcol varchar(1000)
)

create table #temp_t2
(
procname varchar(500)
)

declare @procname as varchar(500)
declare @found as int
declare @colname as varchar(500)
declare @valid_colname as int


select @valid_colname = count(id)
from syscolumns
where name = @colname_para

if (@valid_colname > 1)
begin
set @colname = '%' + @colname_para + '%'



declare sp_cursor cursor
for select name
from sysobjects
where xtype = 'P'

open sp_cursor

fetch next from sp_cursor
into @procname

while @@fetch_status = 0
begin
insert into #temp_t
exec sp_helptext @procname

set @found = 0
select @found = count(textcol)
from #temp_t
where textcol like @colname

if (@found > 0)
begin
insert #temp_t2 values(@procname)
end

delete #temp_t

fetch next from sp_cursor
into @procname
end

close sp_cursor
deallocate sp_cursor


select *
from #temp_t2

drop table #temp_t
drop table #temp_t2
end
else
begin
select 'Please verify column name'
end

end

View 2 Replies View Related

Show All Months In First Column Of Stored Procedure

Aug 26, 2005

Hi,I need what would be similar to a cross tab query in Access.First Column down needs to show all the months, column headings wouldbe the day of the month....1 2 3 4 etc...JanFebMaretchow do i set this up in a stored procedure?any help to get me in the right direction would be greatlyappreciated!!thanks,paul

View 2 Replies View Related

Aliased Column In Stored Procedure Not Seen By Datagrid

Sep 17, 2005

i have an aliased column in an sql statement that works fine whendisplaying its output in a datagrid, but when I transfer the sqlstatement into a stored procedure , the datagrid can't see it. I get anerror "{"DataBinder.Eval: 'System.Data.DataRowView' does not contain aproperty with the name myaliasedcolumn." }

View 1 Replies View Related

Altering Column Fields With A Stored Procedure

Jul 20, 2005

I have some columns of data in SQL server that are of NVARCHAR(420)format but they are dates. The dates are in DD/MM/YY format. I want tobe able to convert them to our accounting system format which isYYYYMMDD. I know the format is strange but it will make things easierin the long run if all of the dates are the same when working betweenthe 2 different databases. Basically, I need to take a look at theyear portion (with a SUBSTRING function maybe) to see if it is greaterthan 50 (there will not be any dates that are less than 1950) and ifit is concatenate 19 with it (ex. 65 = 1965). Then, concatenate themonth and day from the rest to form the date we need in NUMERIC(8).So, a date of January 17, 2003 (currently in the format of 17/01/03)would become 20030117. In VB, the function I would write is somethinglike the following:/*Dim sCurrentDate as StringDim sMon as stringDim sDay as StringDim sYear as StringDim sNewDate as StringsCurrentDate = "17/01/03"sMon = Mid(sCurrentDate, 4, 2)sDay = Mid(sCurrentDate, 1, 2)sYear = Mid(sCurrentDate, 7, 2)If sYear < 50 ThensYear = "20" & sYearElseIf sYear > 50 ThensYear = "19" & sYearEnd ifsNewDate = sYear & sMon & sDay*/I was thinking of doing this in a Stored Procedure but am really rustywith SQL (it's been since college).The datatype would end up being NUMERIC(8). How I would write it if Inew how to write it would be: grab the column name prior to theprocedure, create a temp column, format the values, place them intothe temp column, delete the old column, and then rename the tempcolumn to the name of the column that I grabbed in the beginning ofthe procedure. Most likely this is the only way to do it but I have noidea how to go about it.

View 1 Replies View Related

Passing Column Name As Parameter To A Stored Procedure

Jul 20, 2005

Hi!I want to pass a column name, sometimes a table name, to a storedprocedure, but it didn't work. I tried to define the data type aschar, vachar, nchar, text, but the result were same. Any one know howto get it work?Thanks a lot!!Saiyou

View 1 Replies View Related







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