.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 execute a concatenated query in a procedure

Posted By:      Posted Date: October 21, 2010    Points: 0   Category :Sql Server
I have a procedure which is->
create procedure procedure1(@city varchar(20),@department varchar(20),@name varchar(20))
declare @a varchar(50);
declare @b varchar(50);
declare @c varchar(50);
declare @d varchar(500);
if @city='
set @a=';
set @a='where emp_city='+@city;
if @department='
set @b=';
set @b='and emp_department='+@department;
if @name='
set @c=';
set @c='and emp_name='+@name;
set @d='select * from employees where'+@a+@b+@c;
---???????????????????????????WHAT TO DO TO EXECUTE @d?


Now in the procedure, @d holds a select query but it is in varchar format. I cannot execute that as a query in the procedure.
How to execute the query stored in @d?
pls help.

View Complete Post

More Related Resource Links

Cannot find execute any query, stored procedure not found even if it is there


Hey guys,

I am getting frustrated with this problem, I dont know what i did, but now I cannot execute any stored procedured when I could last time.

When I use my asp.net application to run the query, it finds the stored procedure but when I execute it is sql management studio it says it cannot find the stored procedure even though it is there.

I tried to execute other procedures and the samething happens. Even when I try a simple query it says it cannot find the table

I could execute the query if i placed Use [databasename] in front, but even with this, I cannot execute stored procedures.

does any1 know how to fix this?

Ju Lian

selecting between query and stored procedure

<!-- /* Style Definitions */ p.MsoNormal, li.MsoNormal, div.MsoNormal {mso-style-parent:""; margin:0in; margin-bottom:.0001pt; mso-pagination:widow-orphan; font-size:12.0pt; font-family:"Times New Roman"; mso-fareast-font-family:"Times New Roman";} @page Section1 {size:8.5in 11.0in; margin:1.0in 1.25in 1.0in 1.25in; mso-header-margin:.5in; mso-footer-margin:.5in; mso-paper-source:0;} div.Section1 {page:Section1;} /* List Definitions */ @list l0 {mso-list-id:1033506322; mso-list-type:hybrid; mso-list-template-ids:369124590 67698703 67698713 67698715 67698703 67698713 67698715 67698703 67698713 67698715;} @list l0:level1 {mso-level-tab-stop:.5in; mso-level-number-position:left; text-indent:-.25in;} ol {margin-bottom:0in;} ul {margin-bottom:0in;} --> What would prefer to pull data out from sources stored procedure or query? and why?

Execute system stored procedure

This should be very simple, but I haven't found the solution yet.  I'm writing a Q&D application to setup log shipping for a large number of databases.  I need to execute several stored procedures (e.g. sp_add_log_shipping_primary_database) which will return a value plue two output parameters that I need.  I've taken the code generated by SQL Server and executed it in a SMSS query window.  I've tried configuring an ADODB command with EXEC sp_name parm1, parm2, ..., parm 12 OUTPUT and setup parameters on the command without success (The connection cannot be used to perform this operation.).  I tried stringing all of the statements needed together in one line separated by semi-colons (executed without error but didn't return any values). I'm using VB2010 and SQL Server 2008.  Any suggestions would be appreciated.

Execute SSIS package stored in Database - From Stored Procedure

Dear frnds, I am trying to execute a SSIS package that is stored in a SQL Server 2005 database Want to execute from a stored procedure in same database.  What commands/operations are necessary ? I am also having Two parameter. Regards, sajid 

Any way to execute a workflow when a caml query returns zero items?

Lets say I have a list with a status column and I want to execute a workflow when all items in the list have a status of complete. I basically want to execute a workflow when the caml query looking for non-complete statuses returns zero rows. Can I do this with SPD or are there any 3rd party tools for this? I'm thinking of writing one if there are not.


Hi Everone,    Can any one help me out in creating a dynamic select statement, so far my SP look like belowALTER PROCEDURE [dbo].[CheckAccess]@TABLENAME VARCHAR(100),@JNID INT=0ASBEGIN    SET NOCOUNT ON;    DECLARE @SQ VARCHAR(500)    DECLARE @ID INT =0     SET @SQ= ' SET @ID = SELECT TOP 1 ID FROM ' +  @TABLENAME + ' WHERE PID ='+ @JNID       PRINT @SQ    EXEC(@SQ)       IF (@ID>0)      BEGIN        RETURN 1      END    ELSE      BEGIN        RETURN 0      ENDENDGOlet me know where i am going wrong

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.



Sorry if this is a repeat question but I dont know what I am doing wrong here. I have created a Stored Proc which when you pass in a parameter of SQL Login, it then passes through every database on the server and returns all the perms for that SQL Login on every database. This is a request from one of our Managers you wants to be able to see a users database perms at any point in time by just running the proc. As he is not 'sa' himself, my idea was to create a proc the set it to EXECUTE as SA. So that whenever he ran it he would get the info he needs.

So my proc works fine when I run it as I am 'sa'. I now need to add the "Execute as SA" command but it wont let me as follows

CREATE PROCEDURE dbo.usp_CheckUserPermsAll 
	-- Add the parameters for the stored procedure here
	@user varchar(255)



	-- SET NOCOUNT ON added to prevent extra result sets from

Stored Procedure takes longer than the query.



I'm stuck up in a situation where my sp takes longer time but the query takes lesser time. I went through all the posts mentioned in the T-sql Forum and i tried all the possible resolutions mentioned there but it did not help me.

I have a sp which has around 20 parameters and i tried assigning them to local variables as mentioned in various posts but that did not help me.

Could you please help me out with this problem?







Calling Oracle procedure from SSIS 'Execute SQl task' is not working


Hi ,


Iam using 'Execute SQl task' which calls a stored procedure located in sql server database.The task's SQL source type is variable and the variable has the follwoing expression "EXEC PROC_SEL_MBO_REPORT "+@[User::V_SP_Job_Date]after evaluation it is like EXEC PROC_SEL_MBO_REPORT '01/NOV/2007'.It is working fine

Now the procedure is changed to Oracle.So I have changed it to "BEGIN  PROC_SEL_MBO_REPORT " + "("+ @[User::V_SP_Job_Date]+")"+"; END"+";" after evaluation it is like BEGIN  PROC_SEL_MBO_REPORT ('01/NOV/2007') END;.It is sucessfully executing from the task but no data is loaded into the tables which are used by the procedure internally.
Executing  'execute BEGIN  PROC_SEL_MBO_REPORT ('01/NOV/2007') END;' is perfectly alright from SQl developer or sql plus.

Please help me.. thanks in advance




With Recompile and Execute as clause in SQL 2005 Stored Procedure



I am working on an asp.net application which uses quite a few sql 2005 procedures. Due to some change in requirement I had to modify a couple of the procedures.

During this change, I found that most of the procedures contain clause With Recompile, Execute As Caller.

I am planning to refactor the code in procedure but not sure if I can remove these clauses. Can any one please help me with these?

Thanks in advance.

Export SQL query from Execute SQL in SSIS


I am new to SSIS and I was wondering if it was possible to export a SQL query using Execute SQL query to an excel or text file.

All i need to do is export the results of

SELECT AccountID, BranchID, DepartmentID, ClientName, GroupNumber, PAREA, ProductCode, ProductDescription, SearchType, Quantity, ClientType, Filler,
VRM, VIN, [Transaction Date], TransactionValue
FROM MangoletsiHoldings
WHERE (SearchType = '4') AND ([Transaction Date] BETWEEN '01/08/2010 00:00' AND '05/08/2010 00:00')

to an excel or txt file, any help is much appreciated.



SQL Command Or Stored Procedure in Execute SQL Task


Hi all,

Which is a better way to do in Execute SQL Task : Direct SQL input or Create a stored proc in database and then use that.

In my opinion, Stored Procedure is a better and recommended way for following reasons:

  • Cached execution plan
  • More secure
  • Centralized code
  • Code reuse

Please let me know if this is not the case with SSIS Execute SQL Task.


Why can't I execute a DELETE query in SSIS?


Select * from Table    ->  works

Delete from Table       -> throws error about no instance instantiated

How to execute DELETE stored procedure programmatically (C#)


 I need to execute a stored procedure which is a simple delete (record) query. Programmaticaly, I want to pass in a parameter "ID1".

Assume I will pass in the ID1 parameter on a delete link click event froma GridView control. What would the code be to execute the existing stored procedure?

Also assume:

ID1 - the parameter and primary key of the source database for the record to be deleted

GridView1 - the GridView control

spDeleteRecord - the stored procedure needing the parameter ID1 and to be executed from C# code

Here's my start:

protected void GridView1_RowDeleting(object sender, EventArgs e)
TableCell cell = GridView1.Rows[e.RowIndex].Cells[1];
int ID1 = System.Convert.ToInt16(cell.Text);
??? - code here to call and execute parameter query

Need to execute ALTER DATABASE inside stored procedure


SQL 2005 Standard.

I wrote a sp that uses ALTER DATABASE instruction. When I execute sp with elevated user it works fine; when I try to execute that sp with a normal user (I alerady gave execute permission for that user) it fails with "alter database failed".

I tried to modify create proc with execute as owner but nothing changed.

How can I do ?


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