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

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


Posted By:      Posted Date: September 01, 2010    Points: 0   Category :ASP.Net
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

View Complete Post

More Related Resource Links

Unable to select any stored procedure while creating TableAdapters in wizard


I'm using VS 2008 and SQL 2008.

I have created the tables and the stored procedures in SQL 2008.

In VS 2008, I created DataSet1.xsd in App_Code and created the connectionString in web.config file.

Then when I go into the DataSet1.xsd and try to add a TableAdapter, strange things happened.

First I chose the data connection, then selected "Use existing stored procedure", then there was nothing listed in the dropdownlists (in Select, Insert, Update, or Delete). 

I'm sure the connectionString is correct because if I choose "Use SQL statement" and type in a "select * from mytable1", the TableAdapter can be created without any problem.

Any suggestions?

Entity Framework 4.0 Dynamic Data - stored procedure


I'm using Dynamic Data and have created a custom List.aspx page.  I simply want to populate the grid with a stored procedure.  I've seen some posts about doing this with a LinqDataSource but not an EntityDataSource.  Does someone have an example or a different way of doing this?  Thanks.

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?

Cannot call Table Valued Function from Dynamic stored procedure statement

Hello,  I have a table-valued function that splits string into a table column. I can easily call this function from a stored procedure within a regular SELECT statement: SELECT * FROM Table1 WHERE Code1 = '1' AND Code2 IN(SELECT * FROM [dbo].[fnSplitValues](@Code2String))   However when I try to use the same logic for a dynamic SELECT statement: ‘SELECT * FROM Table1 WHERE Code1 = '1' AND Code2 IN(SELECT * FROM ' + [dbo].[fnSplitValues](@Code2String) + ')’ I get an error ‘Cannot find either column "dbo" or the user-defined function or aggregate "dbo.fnSplitValues", or the name is ambiguous.’ Any idea what is wrong with my dynamic SQL?   Please help, Lana

stored procedure: select and update

Is it possible to use a stored procedure to get a value of the database, change it and save it back at ones? Let me show a simple example: In the databse I have an integer value (20). Then I get another number (5), and I would like to subtract it of the  value from database (20 - 5). That means I have to get the value 20 out of the database, of it subtract value 5, and insert (update) new value (15) back to the database. Or is it not even necessary to get the value 20 out of the databse, but only directly subtract value 5 of the value in the database? Is this possible to do with stored procedure?

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&

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 stored procedure with in select statement



        Any one pls tell me how to call the stored procedure in SQL server

unable to pass dynamic dates to stored procedure with pivot


hi All,

                  I am unable to date as dynamic parameter to stored procdure with pivot.i am getting


Msg 8114, Level 16, State 1, Procedure Sample, Line 3

Error converting data type nvarchar to datetime.

Msg 473, Level 16, State 1, Procedure Sample, Line 3

The incorrect value "@date1" is supplied in the PIVOT operator.

below is my stored procdure



procedure Sample(@date1 datetime,@date2 datetime)



Stored procedure returns two select results


Hey guys

I am creating a buddy list and I need to select my entire buddy list and then the ones that are online

I have two querries one is your standard


FROM buddies


the other is a bit more complicated


SELECT Buddy as Online
FROM Buddies
FROM online 
WHERE online.Buddy = Buddies.Buddy)

I can't use the union command since I create another column

What I would like is to append the column to my original result so I can access it from some vb script using the sqlclient.datareader


Stored Procedure Metadata failure- Dummy select not working


I have a procedure that involves creation of temp table, inserting data into it and use this data to populate another table variable whose output forms the dataset for the OLE DB Source.

Here is the general outlay of the procedure (just a sample data) -


IF 1=0


INSERT the Results of a Stored Procedure with Multiple SELECT Statements into Multiple Tables?

I have a stored procedure (which is overly complex and written by someone else) that I need to take the results of and put them into tables.  The stored procedure uses Dynamic SQL to build and then run 5 SELECT statements based on literally hundreds of variables.  I need to take the results of these SELECT statements and put them into tables in a different database.  Rewriting or re-creating the logic of the stored procedure is NOT AN OPTION as the logic may change over time and maintaining it in multiple places would be nightmarish.  Can anybody help me with this?  I've googled extensively, but have only been able to find examples of using one result set not multiple ones.

MSSQL 2005 JDBC driver multiple select statements in stored procedure



we have a stored procedure with multiple select in it.



Select a,b,c from table1

Select f,g,h  from table2

Select j,k,l  from table3


Sometimes select no 2 will not return any values.

when this happens  we get only 2 result sets using CallableStatement getMoreResults method. 

it seems that if a select  doesn't return something it will  not have a corresponding resultset. 

On 2000 (driver + server) version we got an empty resultset for this situation. 

Is there  any  way to have the same behaivor with 2005 ?   We tryed with 1.2 and 2.0 jdbc driver.




Can we create Stored Procedure from SSAS MDX Query


HI All,

Is this possible to create STORED PROCEDURE TO FROM MDX QUERY,if yes please tell me how and also can we use that STORE PROCESDURE in SSRS report.



Dynamic Pivot in Stored Procedure


i have a dynamic  pivot table which has a parameter passed in stored procdure

i have temp table to store columns in it to be dynamic and also a parameter

but this script make me go mad

colud you find me the error

Inncorrect syntax near '+@columns+'

Item_Group2 table structure is


this is script

	@Group1_ID int
create table #tASD ( ID int,pivot_column varchar(100))
DECLARE @columns VARCHAR(8000),@sql varchar(max)
Select @sql='select *  from Item_Group2 where Group1_ID=('+@Group1_ID+')'
insert into #tASD
SELECT @columns = COALESCE(@columns + ',[' + cast(pivot_column as varchar) + ']',
'[' + cast(pivot_column as varchar)+ ']')


max(ID) FOR [Name]IN (' + @columns + ')

AS p
drop table  #tASD

thanks so much

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

Variable names must be unique within a query batch or stored procedure. ???


Hi i am using sql data source to delete first from Child Table and second check if there is no child  go and delete the primary key in Parent table

this is my code

<asp:SqlDataSource ID="SDSOrdersGrid0" runat="server"
            ConnectionString="<%$ ConnectionStrings:ConnectionString %>"
            DeleteCommand="Declare  @OrderIDinWeek
Set @OrderIDinWeek=(Select order_dtl_id from Weeks where IdWeek=@IDweek)
IF (SELECT count(*) FROM  weeks WHERE order_dtl_id =@OrderIDinWeek) =0
   delete from order_dtl where order_dtl_id= @OrderIDinWeek

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