询问几个T-SQL的写法

[youzi520]

1、在写存储的创建了一个对象 但是存储过程出错了 对象也创建 这样再执行存储就报错

如何先判断是否存在这个对象 先删除这个对象 如对象名为 table1

2、
CREATE TABLE T
(
id INT,
n1 VARCHAR(50),
n2 VARCHAR(50)
)
INSERT INTO T
SELECT 1,'u/L ¦b/L ¦c/s ¦i/j','0.1 ¦5.2 ¦2007-10-10 ¦20 ' UNION ALL
SELECT 2,'b/r ¦d/r','5.6 ¦7.5' UNION ALL
SELECT 3,'f/s ¦d/s','5.6 ¦5.6'




SELECT TOP 50 ID=IDENTITY(INT,1,1) INTO # FROM SYSOBJECTS A,SYSOBJECTS B

DECLARE @sql VARCHAR(8000)

SET @sql = ''

SELECT T_ID =IDENTITY(INT,1,1),A.ID,S=SUBSTRING(n1,b.id,CHARINDEX('¦',n1+'¦',b.id)-b.id)
INTO #1
FROM
(SELECT ID,N1=REPLACE(n1,' ',''),n2 FROM T) A, # B
WHERE B.id <= LEN(n1+'¦') AND CHARINDEX('¦','¦'+n1,b.id) =b.id
ORDER BY A.ID,B.ID


SELECT T_ID =IDENTITY(INT,1,1),n1,Value=SUBSTRING(n2,b.id,CHARINDEX('¦',n2+'¦',b.id)-b.id)
INTO #2
FROM
(SELECT ID,N1=REPLACE(n1,' ',''),n2 FROM T) A, # B
WHERE B.id <=LEN(n2+'¦') AND CHARINDEX('¦','¦'+n2,b.id) = b.id
ORDER BY A.ID,B.ID


SELECT @sql = @sql+',['+s+'] = CAST(ISNULL(MAX(CASE WHEN S = '''+S+'''
THEN Value END),'''') AS VARCHAR(10))'
FROM #1 GROUP BY S
SET @sql = 'SELECT '+STUFF(@sql,1,1,'')

EXEC(@sql+'
FROM #1 A LEFT JOIN #2 B ON A.T_ID = B.T_ID
GROUP BY A.ID ORDER BY A.ID')


DROP TABLE #,#1,#2,TB
DROP TABLE T

b/L b/r c/s d/r d/s f/s i/j u/L
---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
5.2 2007-10-10 20 0.1
5.6 7.5
5.6 5.6

以上得到了查询结果 但是我想对这结果再传递些条件 如何组合到原来的语句中，我试了几次总是报语法错误


比如我想查询b/L=5.2中行 那么
b/L b/r c/s d/r d/s f/s i/j u/L
---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
5.2 2007-10-10 20 0.1

如在增加排序字段 order by b/L

[samfeng_2003]

--不好意思,最近有点忙!收到你的消息都不太好回
CREATE   TABLE   T 
( 
id   INT, 
n1   VARCHAR(50), 
n2   VARCHAR(50)   
) 
INSERT   INTO   T     
SELECT   1,'u/L       ¦b/L       ¦c/s     ¦i/j','0.1       ¦5.2       ¦2007-10-10     ¦20       '   UNION   ALL     
SELECT   2,'b/r       ¦d/r','5.6       ¦7.5'   UNION   ALL           
SELECT   3,'f/s       ¦d/s','5.6       ¦5.6' 


  SELECT   TOP   50   ID=IDENTITY(INT,1,1)   INTO   #   FROM   SYSOBJECTS   A,SYSOBJECTS   B 
  
  DECLARE   @sql   VARCHAR(8000) 

  SET   @sql   =   '' 

  SELECT   T_ID   =IDENTITY(INT,1,1),A.ID,S=SUBSTRING(n1,b.id,CHARINDEX('¦',n1+'¦',b.id)-b.id) 
    INTO   #1 
    FROM   
        (SELECT   ID,N1=REPLACE(n1,'   ',''),n2   FROM   T)   A,   #   B 
      WHERE   B.id   <=   LEN(n1+'¦')   AND   CHARINDEX('¦','¦'+n1,b.id)   =b.id 
      ORDER   BY   A.ID,B.ID 


  SELECT   T_ID   =IDENTITY(INT,1,1),n1,Value=SUBSTRING(n2,b.id,CHARINDEX('¦',n2+'¦',b.id)-b.id) 
    INTO   #2 
    FROM   
        (SELECT   ID,N1=REPLACE(n1,'   ',''),n2   FROM   T)   A,   #   B 
        WHERE   B.id   <=LEN(n2+'¦')   AND   CHARINDEX('¦','¦'+n2,b.id)   =   b.id 
        ORDER   BY   A.ID,B.ID 


  SELECT   @sql   =   @sql+',['+s+']   =   CAST(ISNULL(MAX(CASE   WHEN   S   =   '''+S+''' 
                                                    THEN   Value   END),'''')   AS   VARCHAR(10))' 
    FROM   #1   GROUP   BY   S 
  SET   @sql   =   'SELECT   '+STUFF(@sql,1,1,'') 

  EXEC(@sql+'   
    FROM   #1   A   LEFT   JOIN   #2   B   ON   A.T_ID   =   B.T_ID 
        GROUP   BY   A.ID   ORDER   BY   A.ID') 


DROP   TABLE   #,#1,#2
DROP   TABLE   T       

[youzi520]

我想要u/L=5.2的记录 如何把这条件加上去？？

samfeng_2003 ？？

[playwarcraft]

1.
if exists(select * from sysobjects where name='T' and xtype='U')
drop table T

[youzi520]

3、
SELECT TOP 8000 ID=IDENTITY(INT,1,1) INTO # FROM SYSOBJECTS A,SYSOBJECTS B

DECLARE @sql VARCHAR(8000)

SET @sql = ''



SELECT T_ID =IDENTITY(INT,1,1),A.ID,S=SUBSTRING(col,b.id,CHARINDEX('|',col+'|',b.id)-b.id)
INTO #1
FROM
(SELECT ID,col=REPLACE(col,' ',''),col_v FROM nd_lhsj T) A, # B
WHERE B.id <= LEN(col+'|') AND CHARINDEX('|','|'+col,b.id) =b.id
ORDER BY A.ID,B.ID


SELECT T_ID =IDENTITY(INT,1,1),col,Value=SUBSTRING(col_v,b.id,CHARINDEX('|',col_v+'|',b.id)-b.id)
INTO #2
FROM
(SELECT ID,col=REPLACE(col,' ',''),col_v FROM nd_lhsj T) A, # B
WHERE B.id <=LEN(col_v+'|') AND CHARINDEX('|','|'+col_v,b.id) = b.id
ORDER BY A.ID,B.ID


SELECT @sql = @sql+',['+s+'] = CAST(ISNULL(MAX(CASE WHEN S = '''+S+'''
THEN Value END),'''') AS VARCHAR(10))'
FROM #1 GROUP BY S
SET @sql = 'SELECT '+STUFF(@sql,1,1,'')

EXEC(@sql+'
FROM #1 A LEFT JOIN #2 B ON A.T_ID = B.T_ID
GROUP BY A.ID ORDER BY A.ID')


DROP TABLE #,#1,#2


-----------------------------------------

这是我的存储过程
我想先判断下T里面是否有记录

然后再执行下面的存储 没有则返回空

我这样改了报错误？

SELECT TOP 8000 ID=IDENTITY(INT,1,1) INTO # FROM SYSOBJECTS A,SYSOBJECTS B

DECLARE @sql VARCHAR(8000)

SET @sql = ''

if exists(select 1 from T)

SELECT T_ID =IDENTITY(INT,1,1),A.ID,S=SUBSTRING(col,b.id,CHARINDEX('|',col+'|',b.id)-b.id)
INTO #1
FROM
(SELECT ID,col=REPLACE(col,' ',''),col_v FROM nd_lhsj T) A, # B
WHERE B.id <= LEN(col+'|') AND CHARINDEX('|','|'+col,b.id) =b.id
ORDER BY A.ID,B.ID


SELECT T_ID =IDENTITY(INT,1,1),col,Value=SUBSTRING(col_v,b.id,CHARINDEX('|',col_v+'|',b.id)-b.id)
INTO #2
FROM
(SELECT ID,col=REPLACE(col,' ',''),col_v FROM nd_lhsj T) A, # B
WHERE B.id <=LEN(col_v+'|') AND CHARINDEX('|','|'+col_v,b.id) = b.id
ORDER BY A.ID,B.ID


SELECT @sql = @sql+',['+s+'] = CAST(ISNULL(MAX(CASE WHEN S = '''+S+'''
THEN Value END),'''') AS VARCHAR(10))'
FROM #1 GROUP BY S
SET @sql = 'SELECT '+STUFF(@sql,1,1,'')

EXEC(@sql+'
FROM #1 A LEFT JOIN #2 B ON A.T_ID = B.T_ID
GROUP BY A.ID ORDER BY A.ID')

else

exec('select * from T')

DROP TABLE #,#1,#2


报else附近有语法错误！

[youzi520]

if exists

是如何组合条件语句的？？

[youzi520]

还是第一个问题

CREATE procedure pro_excel
@tjr varchar(50),
@tjrq datetime,
@jyid varchar(50)

as
begin
set nocount on

if exists(select * from sysobjects where name='table1' and xtype='U')
drop table table1


DECLARE @s1 VARCHAR(8000),@s2 VARCHAR(8000)
SELECT @s2=ISNULL(@s2+'+''|''+','') + ' ISNULL(cast([' + name + '] as varchar),''0'')' ,
@s1=ISNULL(@s1+'+''|''+','') + ' ''' + name + ''' '
FROM syscolumns WHERE id=OBJECT_ID('table1')
EXEC('insert into nd_lhsj SELECT ' + @s1 + ' n1,' + @s2 + ' n2,'''+@tjr+''' tjr,'''+@tjrq+''' tjrq,'''+@jyid+''' jyid FROM table1')

drop table table1



end
GO

我使用 报Microsoft OLE DB Provider for SQL Server error '80040e37'

对象名 'table1' 无效。

/webback/oa_jcgl/excel_deal.asp, line 64

？？

[hh1314588]

我把你的语句偷走，我研究研究，我对存储过程不是很熟悉，帮不上你的忙！

[晓风残月0110]

---测试
if object_id('t') is not null
begin
select '表存在'
drop table t
end
else
select '表不存在'

go
create table t (coding varchar(20) not null,a int)

[晓风残月0110]

---测试
if object_id('Getalltest') is not null
begin
select 'proc存在'
drop proc Getalltest
end
else
select 'proc不存在'
--
go
create procedure Getalltest
as

[晓风残月0110]

if exists(select * from sysobjects where xtype='p' and name='Getalltest')
drop proc Getalltest

[samfeng_2003]

SELECT * FROM #1
SELECT * FROM #2
你查查这两个表，在这两个表上做做文章！
最近在整理一个SPRING+HNIBERNATE的框架比较忙！星期六，星期天只能在网吧！所以帮不上什么忙！不好意思了！：）