.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
Gaurav Pal
Post New Web Links

How to check whether a column is been referred in store procedure or not

Posted By:      Posted Date: September 22, 2010    Points: 0   Category :Sql Server

HI Team,

I am about to rename a column in one of the table. How can I check the dependency for the column specially in store procedure? I am using SQL server 2008.

Thanks in advance



View Complete Post

More Related Resource Links

How I can to check if Store Procedure altered(been called )?

I have ado.net connection to SP               conn = new SqlConnection("Data Source=DY;Initial Catalog=DMSA;Integrated Security=True");                 conn.Open();                   // 1.  create a command object identifying                 //     the stored procedure                 SqlCommand cmd = new SqlCommand("dbo.UpdateCorporationFile", conn);                   // 2. set the command object so it knows                 //    to execute a stored procedure                 cmd.CommandType = CommandType.StoredProcedure;                 cmd.Parameters.Add(new SqlParameter("@count_create_C_W", DBNull.Value));        &nb

Help!, Problem with creating my store procedure.

I have two tables. Product_Table with ProductID as the primary key. My other table, ProductSKU_Table which has SKU as the primary key and ProductID as the foreign key. I am trying to create a store procedure to insert into both tables. How do I insert the primary key from the Product_Table into the foreign key of the ProductSKU_Table? This is what I have so far:ALTER PROCEDURE [dbo].[sp_addNewProduct] ( @productName varchar(50) = null, @shortDescription varchar(100) = null, @longDescription varchar(4000) = null, @active bit = null, @category int = null, @sku varchar(11), @skuDescription varchar(200) = null, @orginalPrice money = null, @sellingPrice money = null, @specialPrice money = null, @qtyOnHand int = null, @qtyAtSupplier int = null, @imagePath varchar(200) = null, @releaseDate datetime ) AS BEGIN DECLARE @lastIDInserted int INSERT INTO Product_Table(Name, ShortDescription, LongDescription, Active, CategoryId) VALUES(@productName, @shortDescription, @longDescription, @active, @category) SET @lastIDInserted = @@IDENTITY INSERT INTO ProductSKU_Table(ProductId, SKU, Description, OrginalPrice, SellingPrice, SpecialPrice, QtyOnHand, QtyAtSupplier, ReleaseDate, imagePath, CreatedDate, ModifiedDate) VALUES(@lastIDInserted, @sku, @skuDescription, @orginalPrice, @sellingPrice, @specialP

Most efficient way to check whether a DataGridView contains some specific text at a specific column

Dear All,   I am looking at the most efficient way to check whether a specific column of a DataGridView contains some text. For example I have a list of firs names and I would like to check whether the text "Bob" already exists in that columns. I would like to avoid to loop through each row but what thinking of using something with "Contains".   Anybody has an idea ?   Cheers,   Kalos

Insert into and Store procedure problem

Hello I am trying to create store procedure wich will insert data in temp table CREATE PROCEDURE GetDataForUpdate AS if exists(select * from sys.objects where name='GetDataForUpdate_temp_tb') begin DROP TABLE GetDataForUpdate_temp_tb end go WITH cte as (select Sp.[Item No_], Sp.[Starting Date], It.[No_], It.[Manufacturer Code], It.[Description], Sp.[Unit Price], row_number () over (partition by Sp.[Item No_] order by Sp.[Starting Date] desc) as rn from dbo.[Main-db$Sales Price] AS Sp JOIN dbo.[Main-db$Item] AS It ON Sp.[Item No_] = It.[No_] WHERE Sp.[Sales Code]='RETAIL' AND Sp.[Item No_] LIKE 'I%' ), cte2 as ( SELECT [Item No_],SUM(ISNULL(Quantity, 0)) AS Qty FROM [Main-db$Item Ledger Entry] WHERE [Item No_] LIKE 'I%' AND ( [Location Code] = 'WH-AB-#2' ) GROUP BY [Item No_] ) SELECT cte.[Manufacturer Code],cte.[Description], CONVERT(int, cte2.[Qty]) AS 'Qty',CONVERT(int, cte.[Unit Price]) AS 'Unit Price' INTO GetDataForUpdate_temp_tb FROM cte JOIN cte2 ON cte.[Item No_]=cte2.[Item No_] WHERE rn = 1 ORDER BY cte.[Item No_],[Starting Date] But after execute, lefts only this part of query in store procedure: set ANSI_NULLS ON set QUOTED_IDENTIFIER ON go ALTER PROCEDURE [dbo].[GetDataForUpdate] AS if exists(select * from sys.objects where name='GetDataForUpdate_temp_tb') begin DROP TABLE GetDa

SSIS - Call a Store procedure

Can someone show me an example of how to call a store procedure that checks for empty fields in a SQL database table, by returning a value when fields are empty. then send an email to the adminstratore.

How to use Store Procedure to execute the Function Of COM DLL (the DLL is Coded By C#)

Hi All, I find a question. How to use Store Procedure to execute the function of Dll? I tried as the steps: 1. Code a dll 2. Register the dll by "regasm XXX.dll /codebase" 3. Use the dll in SP But when I do it in step 3, I face some error, which is "Invalid class string" and the error ID is "0x800401F3" Could you help me solve this problem? Thank you very much.

Store Procedure

How can i insert and delete a data with use of one store procedure ........... SP File:- ALTER PROCEDURE dbo.One @id int, @Name Varchar(50), @select char(1) AS begin  if @select ='i' insert into j (id,Name) values (@id ,@Name) end begin if @select ='d' delete from j  where id= @id end RETURN How can i called tis two query

how check username and password store in database

hi i have login page. how check username and password store in database . plz reply with code.i did it by form authentication b ut it not work.

update stored procedure for existing values of column(salperyr)

tbl_salary salary           salperyr   hike20      hike20yr10000.0000    NULL      12000.00    144000.0012000.0000    NULL      14400.00    172800.0014000.0000    NULL      16800.00    201600.0015000.0000    NULL      18000.00    216000.0018000.0000    NULL      21600.00    259200.0020000.0000    NULL      24000.00    288000.0022000.0000    NULL      26400.00    316800.00in above table salaryper yr (salperyr) has to be modified after caliculation my null values has to be removed and place (salary*12)in one shot.so plz suggest me update stored procedure for this.

Deploying taxonomy term store and managed meta column from development machine to production machine

Hi, In my development environment, I created a taxonomy term store by importing a CSV file. I also created a site column of type "managed meta data" based on that term store. Now, I want to migrate to production environment. What is the best way to do this? When I generate the schema of my site column, it looks like this: <Field Type="TaxonomyFieldTypeMulti"    DisplayName="Article Type"    List="{d8b20e4f-ce12-4def-b59d-d44da2c13234}"    WebId="eef74e7c-a852-4c89-b811-e31b86853641"    ShowField="Path1033"    Required="FALSE"    EnforceUniqueValues="FALSE"    Group="Custom Columns"    ID="{47f78411-65fa-43c9-8cb8-68955aea1256}"    SourceID="{eef74e7c-a852-4c89-b811-e31b86853641}"    StaticName="Article_x0020_Type"    Name="Article_x0020_Type"    Version="4"    Mult="TRUE"    Sortable="FALSE"> <Default></Default> <Customization> <ArrayOfProperty> <Property> <Name>SspId</Name> <Value xmlns:q1="http://www.w3.org/2001/XMLSchema" p4:type="q1:string" xmlns:p4="http://www.w3.org/2001/XMLSchema-instance">99d

update stored procedure for existing values of column(

tbl_salary salary            salperyr    hike20       hike20yr 10000.0000    NULL      12000.00    144000.00 12000.0000    NULL      14400.00    172800.00 14000.0000    NULL      16800.00    201600.00 15000.0000    NULL      18000.00    216000.00 18000.0000    NULL      21600.00    259200.00 20000.0000    NULL      24000.00    288000.00 22000.0000    NULL      26400.00    316800.00 in above table salaryper yr (salperyr ) has to be modified after caliculation my null values has to be removed and place (salary*12 )in one shot.   so plz suggest me update stored procedure for this

in need of sql 2005 e-book on store procedure

i need e-book on sql 2005 express on STORE PROCEDURE TUTORIAL including how to use IF AND ELSE STATEMENT in SQL

Create store Procedure for select Table by Passing Table Name In Parametere

I want to create a Procudure to Select all Information in Table Like (select * from TableName) but Condition is I do not want to Mention The Fix Table Name I will send the Table Name In a parameter from My Vb.net Project like This (select * from @Parameter ) and Then The Select Procudure work with on That Table. It will be change depend on my vb.net reqierment I already Created a Procudure But it Does not working. It is in below. alter proc AllSelect (@Table varchar (30)) as DECLARE @sql nvarchar(MAX) SET @sql = ' Select * from '+@Table+' ' If possible Please let me know also Update and Delete proc by same way. with Regards Suman Bangladesh

Its posible to do a select without the table and the column names into a stored procedure?



I need your help. Imagine that I have two tables:

  1. ALUMN wich has three columns: IdAlumn, Name and Address.
  2. SPORT wich has columns: IdSport, Name, Players and Description.

Supose that my tables could be differnced by a number 1 is for ALUMN and 2 is for SPORT.

And the columns of each table too. So 1 is for IdAlumn, 2 is for Name and 3 is for Address. 1 is for IdSport, 2 is for Name 3 is for Players and 4 is for Description.

For example the combination of table 2 and column 3 is SPORT-Players.

Then I want to do an stored procedure that receives two parameters (the table, the column) @tab and&

How to run a store procedure as background Job ?



I have scenario where i need to run my store procedure on specific time frame ? For instance i have a field in my table1 called count.

  • If the value of count is 1 for a specific row in table then rightaway the specific row will move to another table2.
  • If the value of count is 2 for a specific row in table1 then the specific row will move to another table2 after 10 min not right away.

i think for this some job should continous run in background, I have store procedure and i want to run that store procedure as background job in MSSQL continous. How can we do that or any other approch? please suggest me.


JP Sharma

How come the same store procedure call can display different results?


Hi all,

This is the sproc I am calling from my Asp.Net application:

ALTER PROCEDURE [Uds].[Dashboard_GetJobs2]
 @date as datetime = null
 Select * From Uds.RealtimeLogging
 Where (@date is null or [timeStamp] > @date)

If I ran from query analyzer I get the correct results:

 exec Uds.Dashboard_GetJobs2 '9/20/2010 11:01:23 PM'

However, if I ran the Asp.Net page, ALL records are showed. Looking at the SQL Profiler I see the following call:
 exec sp_executesql N'exec Uds.Dashboard_GetJobs2',N'@date nvarchar(21)',@date=N'9/20/2010 11:01:23 PM'

And if I ran the statement above in query analyzer it WILL return ALL records. Why????

Thank you


how to debug store procedure in SQL2008?


I remeber I can use analyzer to debug sql in 2000, and it seems it does not have the analyzer in sql 2008 any more. Here is what I do:


1. select the SP, then script SP to->execute to->new window.

2. click debug.


then step into the SP line by line.


Is this the correct way to do it? one problem is: I will have to script the input into the SP directly. Is it possible to let sql pop the window and ask for input like analyzer does?




ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend