pls help for the script!

hello,

my script do not run with the parameter "
exec as @firma='LG', @arbg='01', @hierarchie=1" becomes the following
failure:

"... no convert char in numeric on column"!"


if i run the script with @firma='01' is this ok.

here is the script:

if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[as]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[as]
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

achtung

*/

CREATE procedure as
@firma as char(2)='00',
@arbg as char(2)='00',
@hierarchie as int=1

as



declare
--@firma char(2), @arbg char(2), @hierarchie int,
@tabellenname char(30), @sql nchar(500),
@apostroph char(1), @orgarbg char(7), @laufvar int, @spalte char(2),
@spaltenname char(50),
@spalte_prev char(2), @spaltenname_prev char(50)


select @tabellenname='Fa'+@firma+'_ORG'+@arbg+'_Hier'+cast(@hierarchie
as char(2)),
@apostroph='''',
@orgarbg='kmorg'+@arbg

--Tabelle erstellen---------------------------------------------------------------------------
select @sql='if exists (select * from dbo.sysobjects where id =
object_id(N'+@apostroph+'['+ltrim(rtrim(@tabellenname))+']'+@apostroph+')
and OBJECTPROPERTY(id, N'+@apostroph+'IsUserTable'+@apostroph+') = 1)
drop table ['+ltrim(rtrim(@tabellenname))+']'

exec sp_executesql @sql

select @sql='create table
['+ltrim(rtrim(@tabellenname))+'](['+@orgarbg+'] char(20))'

exec sp_executesql @sql

-----------------------------------------------------------------------------------------------
--Spalte 1 füllen------------------------------------------------------------------------------

select @sql='insert into '+ltrim(rtrim(@tabellenname))+'('+@orgarbg+')
select ororg from kor00
where orarbg='+@arbg+' and orfnr='+@firma+' and (orbuch=''B'' or
orhber=1)'

exec sp_executesql @sql

------------------------------------------------------------------------------------------------
---Spalten aufbauen und
füllen------------------------------------------------------------------


set @laufvar=1
--Dummy-Tabelle Spaltenname erstellen
if exists (select * from dbo.sysobjects where id =
object_id(N'[Spaltennamen]') and OBJECTPROPERTY(id, N'IsUserTable') =
1)
drop table [Spaltennamen]
create table [Spaltennamen]([Laufvar] int, [Spaltenname] char(50))
--

while @laufvar<21

begin

select @spalte=case when @laufvar<10 then '0'+cast(@laufvar as
char(1)) else cast(@laufvar as char(2)) end

--Dummy-Tabelle Spaltenname füllen
select @sql= 'insert into spaltennamen select '+cast(@laufvar as
char(2))+' , case when hebez is null then
'+@apostroph+'Spalte'+@spalte+@apostroph+' else hebez end from xhs00
left join xhe00 on hsanw=heanw and hsarbg=hearbg and hsfnr=hefnr and
hsbr'+@spalte+'=heber where hsanw='+@apostroph+'K'+@apostroph+' and
hshi='+cast(@hierarchie as char(2))+' and hsfnr='+@firma+' and
hsarbg='+@arbg
--select @sql
exec (@sql)
----------------------------------
--Spalte zu Tabelle hinzufügen

select @spaltenname=spaltenname from spaltennamen where
laufvar=@laufvar

--Spalten für Elemente hinzufügen
select @sql='alter table '+rtrim(@tabellenname)+' add Level'+
rtrim(@spalte) +' char(50) null'

exec sp_executesql @sql

--Spalten für Elementbezeichnungen hinzufügen
select @sql='alter table '+rtrim(@tabellenname)+' add '+
rtrim(@spaltenname) +' char(50) null'
exec sp_executesql @sql
-------------------------------


--Spalten in Tabelle füllen
--Level füllen
select @sql='update '+ltrim(rtrim(@tabellenname))+' set
level'+ltrim(rtrim(@spalte))+'= hio'+ltrim(rtrim(@spalte))+' from
'+ltrim(rtrim(@tabellenname))+', kor00, khi00 where orarbg='+@arbg+'
and orfnr='+@firma+' and '+@orgarbg+'=hio01 and orarbg=hiarbg and
orfnr=hifnr and hihi='+cast(@hierarchie as char(2))+' and
ororg=hio'+@spalte
exec sp_executesql @sql
--NULL-Felder Level füllen
select @sql='update '+ltrim(rtrim(@tabellenname))+' set
level'+ltrim(rtrim(@spalte))+'='+@apostroph+'-'+@apostroph+' from
'+ltrim(rtrim(@tabellenname))+' where level'+ltrim(rtrim(@spalte))+'
is null'
exec sp_executesql @sql
--Bezeichnung füllen
select @sql='update '+ltrim(rtrim(@tabellenname))+' set
'+ltrim(rtrim(@spaltenname))+'=
ltrim(rtrim(level'+ltrim(rtrim(@spalte))+'))+'' ''+orbez from
'+ltrim(rtrim(@tabellenname))+', kor00, khi00 where orarbg='+@arbg+'
and orfnr='+@firma+' and '+@orgarbg+'=hio01 and orarbg=hiarbg and
orfnr=hifnr and hihi='+cast(@hierarchie as char(2))+' and
ororg=hio'+@spalte
exec sp_executesql @sql
--NULL-Felder Bezeichnung füllen
select @sql='update '+ltrim(rtrim(@tabellenname))+' set
'+ltrim(rtrim(@spaltenname))+'='+@apostroph+'-'+@apostroph+' from
'+ltrim(rtrim(@tabellenname))+' where '+ltrim(rtrim(@spaltenname))+'
is null'
exec sp_executesql @sql


select @laufvar=@laufvar+1

end

--Spalten Ebene 1 füllen, für Elemente, die nicht in Hierarchie
sind---

select @spaltenname=spaltenname from spaltennamen where laufvar=1
select @sql='update '+ltrim(rtrim(@tabellenname))+' set level01=
'+@orgarbg+' from '+ltrim(rtrim(@tabellenname))+' where level01 is
null'
exec sp_executesql @sql
select @sql='update '+ltrim(rtrim(@tabellenname))+' set
'+ltrim(rtrim(@spaltenname))+'= ltrim(rtrim('+@orgarbg+'))+'' ''+orbez
from '+ltrim(rtrim(@tabellenname))+' left join kor00 on ororg=level01
where '+ltrim(rtrim(@spaltenname))+' is null'
exec sp_executesql @sql


-- Anpassen Elementname --> Schreibe Parentname in Hierarchiesprünge
set @laufvar=19
while @laufvar>1
begin
select @spaltenname= spaltenname from spaltennamen where
laufvar=@laufvar
select @spaltenname_prev= spaltenname from spaltennamen where
laufvar=@laufvar+1

select @sql='update '+ltrim(rtrim(@tabellenname))+' set
'+ltrim(rtrim(@spaltenname))+'= '+ltrim(rtrim(@spaltenname_prev))+ '
where '+ ltrim(rtrim(@spaltenname))+ '=' +@apostroph+'-'+@apostroph

exec sp_executesql @sql

select @spalte=case when @laufvar<10 then '0'+cast(@laufvar as
char(1)) else cast(@laufvar as char(2)) end
select @spalte_prev=case when (@laufvar+1)<10 then
'0'+cast((@laufvar+1) as char(1)) else cast((@laufvar+1) as char(2))
end

-- Anpassen Elementkey --> Schreibe Parentkey+Level in
Hierarchiesprünge

select @sql='update '+ltrim(rtrim(@tabellenname))+' set
level'+ltrim(rtrim(@spalte))+'= ltrim(rtrim(
level'+ltrim(rtrim(@spalte_prev))+ ')) + '
+@apostroph+ltrim(rtrim(@spalte))+@apostroph+' where
level'+ltrim(rtrim(@spalte))+'=' +@apostroph+'-'+@apostroph
select @sql
exec sp_executesql @sql

set @laufvar=@laufvar-1

end


GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO


thanks paul
.

Relevant Pages

  • Re: Give more than one SQL user permissions to a DB
    ... Are you asking for a script so that you don't have to click away using the ... SQL query. ... EXEC sp_defaultdb 'MyDomain\User1', 'MyDatabase' ... Anyway is there an easy way of give all 200 users access to this DB ...
    (microsoft.public.sqlserver.security)
  • Re: Full text catalog just not populating
    ... exec sp_defaultdb N'NT Authority\System', N'master' ... means either the network guys in my company who don't know SQL but are admins ... > needs this login to log into SQL Server and you can either add back this ... >> fetching U ...
    (microsoft.public.sqlserver.fulltext)
  • Re: Indexing delay for one row.
    ... script and WAITFOR and varying the delay from 1 to 18 seconds. ... could you confirm your exact version of SQL Server that you are seeing ... It is possible that a change was made to the pooling frequence under SP3 to ... exec sp_fulltext_table 'FTSTable','create','FTSCatalog','FTSTable_IDX' ...
    (microsoft.public.sqlserver.fulltext)
  • Re: storing and searching office docs in SQL
    ... You CAN both store and search the contents of the MS Word ... files stored in an SQL Table's FT-enable IMAGE column, ... FTS CONTAINS or FREETEXT to search the contents of that MS word document: ... exec sp_fulltext_database 'enable' -- only do this once! ...
    (microsoft.public.sqlserver.programming)
  • Re: SqlDataAdapter1.SelectCommand.CommandType= CommandType.StoredProcedure
    ... > kann man beim EXEC PROC keine Parameter beifügen. ... CommandType.StoredProcedure wird intern als RPC Command abgesetzt, ... SQL RPC siehe SQL Server Dokumentation, ... nach der Ausführung ungültig werden. ...
    (microsoft.public.de.german.entwickler.dotnet.datenbank)