Error in SP - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Error in SP

Author

Topic

Derick
Starting Member

17 Posts

Posted - 2009-07-13 : 13:10:19

Hi All,
When I exec the code below in Server Management Studio it works fine. But when I attach it to a SQLDataSource in my app I get the error ant the end of this Message.

ALTER PROCEDURE [dbo].[elearning_course_table] -- 2526
-- Add the parameters for the stored procedure here
@ref varchar(10)
AS

BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

Declare @SQL VarChar(Max)

begin try

Set @SQL = ''
Select @SQL = @SQL + ' (Select ''' + module_lookup.modCode + ''' as ModuleCode, '''
+ module_lookup.ModuleName
+ ''' From SF_LearnDesignation Where ID = ''' + @ref + ''' and ['
+ Column_Name
+ '] = ''1'')'
+ ' Union All'
From INFORMATION_SCHEMA.COLUMNS INNER JOIN
module_lookup ON INFORMATION_SCHEMA.COLUMNS.COLUMN_NAME = module_lookup.ColumnName
Where Table_Name = 'SF_LearnDesignation'
And Column_Name Like 'DoCourse%'
order by ordinal_position

If Right(@SQL, 9) = 'Union All'
Set @SQL = Left(@SQL, Len(@SQL)-9)
else
Set @SQL = Left(@SQL, Len(@SQL))

Execute(@SQL)
--print (@SQL)

end try
begin catch
print ERROR_MESSAGE()
end catch
end

---------------------------
Visual Web Developer 2008 Express Edition
---------------------------
Unable to retrieve schema. Ensure that the ConnectionString and SelectCommand properties are valid.

Invalid length parameter passed to the SUBSTRING function.
---------------------------
OK
---------------------------

Thanks for any help

Do something out of character each day this week. Change your hairstyle, wear a different colour, show your teeth when you smile, eat chocolate-anything! Just choose to be different because you can!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-07-13 : 13:32:51

whats the need of dynamic sql here?

Derick
Starting Member

17 Posts

Posted - 2009-07-13 : 13:43:05

It is all in the structure of the table. The names of the courses to be done is the column names of the table. This table is done by a trird party developer. I need to report per ID no what module need to be done. Also a column can be added without warning.

Do something out of character each day this week. Change your hairstyle, wear a different colour, show your teeth when you smile, eat chocolate-anything! Just choose to be different because you can!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-07-13 : 13:44:53

so is it like unpivoting? then why dont you use unpivot operator with dynamic sql?

Derick
Starting Member

17 Posts

Posted - 2009-07-13 : 13:53:03

Jip same as unpivoting, with unpivoting you need to hard code the column names. Here I cannot do that as the column names could change or new could be added.

Do something out of character each day this week. Change your hairstyle, wear a different colour, show your teeth when you smile, eat chocolate-anything! Just choose to be different because you can!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-07-13 : 13:58:21

nope. you can get list of column names dynamically using INFORMATION_SCHEMA.COLUMNS view and then pass the list dynamically within UNPIVOT to get them as values.

Derick
Starting Member

17 Posts

Posted - 2009-07-13 : 14:12:39

I think I'll have to go and check up more on unpivoting

Do something out of character each day this week. Change your hairstyle, wear a different colour, show your teeth when you smile, eat chocolate-anything! Just choose to be different because you can!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-07-13 : 14:17:41

good luck. let us know how u got on

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources