Linq to SQL and stored procedure with dynamic sql

I have a stored procedure like

create proc test
as
.....
set @sql = 'select ' + .... + ' from ....'
exec(@sql)

The select list is dynamically (in fact, pivot table). However, after drag the stored procedure to dbml design view and called, the stored procedure only return an integer.

How to get the result set of the stored procedure when using dbml?

Thanks,

 

0
nkw
3/28/2008 4:53:29 PM
asp.net.object-datasource 16182 articles. 0 followers. Follow

3 Replies
1845 Views

Similar Articles

[PageSpeed] 42

Is this the only SELECT statement that @sql gets?  I created a simple example and it performed as I would expect returning a complete recordset....I'm wondering if your stored proc is returning 2 record sets....for reference here's my code...:

 

CREATE PROCEDURE [dbo].[sptest]
	-- Add the parameters for the stored procedure here
	
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	
declare @sql varchar(500)
    -- Insert statements for procedure here
	SELECT @sql = 'select * from staff'
    exec (@sql)
END

 

DataClasses1DataContext dc = new DataClasses1DataContext();
var staff = dc.sptest();
GridView1.DataSource = staff;
GridView1.DataBind();
 
Flames are for BBQ'ing.
http://weblogs.asp.net/RyanSmith
0
RyanSJedi
3/28/2008 8:37:40 PM
0
ecbruck
4/1/2008 1:54:57 PM

Yes, I tried your procedure and it works. Mine is more complex pivot query. What's interesting is that dbml cannot figure out the return type when the dynamic query become complexer.

0
nkw
4/2/2008 4:23:01 PM
Reply:

Similar Artilces:

LINQ in a SQL CLR Stored Procedure (SQL 2008)
Hey all, i'd like to use LINQ in a CLR Stored Procedure in SQL 2008. The problem is, i can't add a reference to System.Xml.Linq. How do i make that happen? my SQL project is targeting .net 3.5, and i have SP1 installed.I saw a blog about this somewhere recently but now i can't find it. so if you can point me to something, that would be awesome.thanks,-b I googled and found this blog http://oakleafblog.blogspot.com/2007/09/problems-using-stored-procedures-for_08.html  Beware of bugs in the above code; I have only proved it correct, not tried it. (Donald Knuth) That does...

Stored Procedures
I am trying to create a StoredProcedure (below) that requires more than one SQL statement to be executed. I am getting a batch error as follows: Result set not permitted in '<batch statement>'. I am using ASA9 - version 9.0.1751. The following is the stored procedure that I am trying to execute. ALTER PROCEDURE "DBA"."asi_SDSelectUserDetailsRange" ( @lrange varchar(5), @hrange varchar(5), @Filter varchar(20), @ColumnName varchar(20) ) BEGIN DECLARE @SQL1 varchar(8000); DECLARE @SQL2 varchar(8000); DECLARE @SQL3 varchar(8000); D...

Problem accessing SQL Stored Procedure From .Net using hibernate object
Hi, I am calling a SQL Stored procedure from .net application using Hibernate object, but somehow its telling me "The 'callable' attribute is not declared.", I am not sure what's wrong I have done here. Will really appreciate if someone can help me for this. Herewith I am providing my Stored Procedure, my .hbm file and code to call it. can someone pls tell me what's wrong in this? Also, if possible, pls tell me how to set result in a list object. Thanks in advance, ######################################### MY STORED PROCEDURE : ALTER PROCEDURE [dbo].[RulesDetails_...

Linq to SQL. sproc or dynamic sql?
Im of the old school belif that all data should come from a DB via sprocs for many reasons that i wont go into here. Im getting into website creation and want to call data from my db via linq to sql. I will want to have some form of pagination. is this easily possible using sprocs( without modifying the sprocs) or are you better just use dynamic sql and skip and take keywords? Hi, Please refer to the following link below for the information about "LINQ to SQL (Retrieving Data Using Stored Procedures)" http://weblogs.asp.net/scottgu/archive/2007/08/16/linq-to-sql-part-6-retrie...

Stored Prodesure and Dynamic SQL for SQL injections ??
hi friends sqldatasource1.selectcommand="select * from table1 where username=@username" sqldatasource1.selectparamaters.add=("username", textbox1.text) sqldatasource1 from data of toolbox and it connects gridview1.. Should i use stored prosedure for security ? or my code is enough ? my second question is.. if i dont use textbox (i mean user cant enter a value for username), should i use paramaters ? or can i use only "select * from table1 where username='John'"Mark as me if my question or my answer can be helpful for you :) Hey, Using comman...

Copy stored procedures from SQL to SQL DE?
Is there an easy way to copy all stored procedures from an SQL server to en SQL Desktop Engine or do I have build all stored procedures by hand?Johan TheunissenMCPD, MCSE, MCTS BizTalk 2006==============================Please mark the most helpful reply/replies as "Answer". You can use the SQL Server import/export wizard, or you can generate a script of them using SQL Server Management Studio. Thanx, In the mean time I copied them manually (copy-paste). So I think a seperate script would be usefull when you have 100+ stored procedures. Or have the script available.Johan Theu...

VB.NET SQL stored procedure: procedure has no parameters and arguments were supplied
VB.NET SQL stored procedure: procedure has no parameters and arguments were supplied Please assist me:This erorr message is produce when calling the stored procedure in vb.netProcedure AutomateMatterNumber has no parameters and arguments were supplied."MS SQL 2000stored procedure:*/CREATE PROCEDURE dbo.AutomateMatterNumber ASDECLARE @nextMtr AS BIGINTDECLARE @dtToday AS DATETIMEIF NOT EXISTS(SELECT * FROM tempMatter WHERE DATEDIFF(dd,DateSet,GETDATE())=0 )BEGINDELETE FROM tempMatter-- incase there are some old recordsSELECT TOP 1 @nextMtr= CONVERT(BIGINT, MatterNumber) + 1 ,@dtTod...

Dynamic SQL in stored procedures...
I cannot find the correct syntax to do selects on columns and tables dynamically .. Example: (DOES NOT WORK..) begin declare @Col char(20) declare @Tbl char(20) select @Col='order_id' select @Tbl='orders' select @Col from @Tbl end This returns error: Table '@Tbl' not found In a stored procedure you must use Execute Immediate: EXECUTE IMMEDIATE 'SELECT ' + @col + ' from ' + @Tbl -- Jim Egan [TeamPS] Dynamic Data Solutions, Inc. http://www.dyn-data.com Houston, TX **********************************...

Stored Procedures in LINQ to SQL ?
 Hi,I am using stored procedures with LINQ and I use it in such way:var result = MyDate.Procedure1.Skip(10).Take(10); In procedure there is select statement.And my question is if it sends all reslult of select or take only 10 elements of it (of course I mean how SQL server works)? I am using it in paging that is why I need to know if I have to 'remake' procedure or find another way to make my GridView faster. Hi, You aren't getting any paging on Procedure1, meaning that all of the data returned from Procedure1 is retrieved every time.  What's happening is t...

Dynamic SQL in stored procedure
I'm trying to build a stored procedure that returns a result set from a select. The purpose of the select statement is to get a result set with one column for each column in table A and also one column for each _row_ in table B. From what I've read here, I can't execute immediate a select which returns results, so I've written the procedure so that the select starts with a 'insert into #temp'. I've got two long varchar's, one for building the syntax for declaring a local temporary table, and one for building the syntax for 'insert into ... selec...

Dynamic Sql in stored Procedure
Hi Everyone, I wanted to know is there a way to create a stored procedure in which a sql string is created and then it can be executed in the stored procedure. Eg. create procedure stroc_proc ( input_param ) returns ( ) as begin str = 'select sql1 from table1'; execute statement str; end help is really appreciated. Thanks in advance. "Mita Acharya" wrote in message news:183181@forums.codegear.com... > > I wanted to know is there a way to create a stored procedure in which a > sql string is created and then it can be executed in the stored proce...

Dynamic SQL in Stored Procedure.
Hi ALL Experts, I tried to create stored procedure that use dynamic sql in Sybase but it failed in temp_1 , the error I got is -----Error begin ------- Incorrect syntax near the keyword 'execute'. State:S1000,Native:156,Origin:[INTERSOLV][ODBC SQL Server driver][SQL Server] -----Error end---- the two sp are as SAMPLE below, it work in default database : pubs (NOTE :This SPs work in MSSQL2000) ======SAMPLE BEGIN========== CREATE proc temp_2 As begin SElect au_fname, au_lname from authors end Create proc temp_1 As Begin Create table #temp1 (t_value1 va...

Dynamic SQL in the stored procedure
We have noticed, that the use of Dynamic SQL in the stored procedure reduces the stored procedure performance. Does this mean that as the dynamic sql statement is not known till it is going to be executed, then the stored procedure is recompiled each time and the execution plan is recreated, or at least the plan for the dynamic SQL part of the procedure has to be created each time, or it is because the use of "EXEC" slows down the query. Has any body else noticed this? If my observation is true, then this means that one must avoid the use of dynamic sql as much as possible...

Dynamic SQL from Stored Procedure
This is a multi-part message in MIME format. --------------74E5864516F71AFE56145D5F Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit I would like to do something like the following in a stored procedure: declare ls_where varchar; delcare ls_name char(40); set ls_where = 'WHERE PERSONCODE IN (1, 2, 3)'; declare cursor .... as SELECT NAME INTO :ls_name FROM PERSON ls_where; I Realise the syntax is not quite right but you can get the idea. Is this possible? Regards, Simon. --------------74E5864516F71AFE56145D5F Content-...

Web resources about - Linq to SQL and stored procedure with dynamic sql - asp.net.object-datasource

Parliamentary procedure - Wikipedia, the free encyclopedia
... of the House of Commons of the Parliament of the United Kingdom , from which it derives its name. In the United States, parliamentary procedure ...

Procedure is more a snap than a snip
A QUEENSLAND doctor is bidding to set an unusual world record by performing the highest number of vasectomies in one day, with the help of fellow ...

Rushed cosmetic procedures a 'recipe for disaster'
&#8203;When Chanelle O'Hare went searching online for a deal on potential cosmetic procedures, she could not have imagined that what she ended ...

Jetstar procedures under investigation after planes took off too heavy
Jetstar's procedures for calculating the weight of its aircraft are under review by the Australian Transport Safety Bureau after two of its planes ...

Old Lady Lawyer: Uncivil Procedure
What is some of the worst behavior you've witnesses by attorneys?

NFL Announces Changes to Officiating Procedures for Playoffs - Bleacher Report
The NFL formally approved changes to its postseason officiating procedures to allow referees the opportunity to consult Vice President of Officiating ...

We need to reform the culture of law enforcement, not just the procedures
We need to reform the culture of law enforcement, not just the procedures by digby I have a new piece up at Salon this morning about police ...

Will Paul Ryan Make His Mark As Speaker By Instituting Impeachment Procedures Against President Obama ...
Wisconsin Ayn Rand devotee Paul Ryan just started his new job as Speaker of the dysfunctional House Republicans. And he's already headed for ...

TSA Updates Screening Procedure, Will Mandate Some Passengers Use Full-Body Scanners
... Imaging Technologies, or AIT, in favor of full-body pat-downs by TSA agents. Under the new mandate, not everyone can opt for the pat-down procedure. ...

'Painless' dental cavity procedure regrows tooth enamel
... "cavity" a lot of people sweat thinking about painful injections and relentless drilling. But scientists in Britain have developed a new procedure ...

Resources last updated: 1/6/2016 11:42:00 AM