将值放入select语句中 [英] Place value into select statement

问题描述

我的SQL中有以下代码。一切都好。可以从数据库表中检索该值，但是如何将值（如下所示计算）设置为select语句并显示出来？

Hi i have the following codes in my SQL. All things are ok. The value can be retrieved from the table of database but how can i set the value(count as in below) to a select statement and display it out?

CREATE TABLE #tempStaTable
	(sta0 VARCHAR(50), sta1 VARCHAR(50), sta2 VARCHAR(50), sta3 VARCHAR(50), sta4 VARCHAR(50), sta5 VARCHAR(50), sta6 VARCHAR(50), sta7 VARCHAR(50))
	
	SET @TABLE = '#tempStaTable'
	
	SET @cursor = CURSOR FOR
	SELECT dbo.CRF_Project.DocStatus
	FROM vsCustomizationRequest.dbo.CRF_Project
	WHERE CustomerID = '1'
	--GROUP BY CustomerID, DocStatus
	
	OPEN @cursor
	FETCH NEXT
	FROM @cursor into @status
	WHILE @@FETCH_STATUS = 0
	BEGIN
		--HERE
		--PRINT @status
		IF(@status = 0)
			SET @COUNT = @COUNT + 1
			SET @varCount = CONVERT(VARCHAR(50), @COUNT)
			SET @ColumnName = 'sta0'
			SET @statement = 'UPDATE #tempStaTable SET '+@ColumnName+' = '+@varCount+''
			EXEC (@statement)
			
		IF(@status = 1)
			SET @COUNT1 = @COUNT1 + 1
		IF(@status = 2)
			SET @COUNT2 = @COUNT2 + 1
		IF(@status = 3)
			SET @COUNT3 = @COUNT3 + 1
		IF(@status = 4)
			SET @COUNT4 = @COUNT4 + 1
		IF(@status = 5)
			SET @COUNT5 = @COUNT5 + 1
		IF(@status = 6)
			SET @COUNT6 = @COUNT6 + 1
		IF(@status = 7)
			SET @COUNT7 = @COUNT7 + 1
						
		FETCH NEXT
		FROM @cursor into @status
		
	END
	PRINT @COUNT
	PRINT @COUNT1
	PRINT @COUNT2
	PRINT @COUNT3
	PRINT @COUNT4
	PRINT @COUNT5
	PRINT @COUNT6
	PRINT @COUNT7
	
	CLOSE @cursor
	DEALLOCATE @cursor
	
        --the problem is at here, i cant display the count value
	SELECT * FROM #tempStaTable

推荐答案

你可以将@ Count1设置为@ Count7作为全局变量，并在forloop游标中设置值。



然后像这样使用PRINT'Count1'+ convert（varchar（7），@ Count1）。

you can set @Count1 to @Count7 as a global variable and set the value in forloop cursor.

then use PRINT 'Count1' + convert(varchar(7),@Count1) like this.

在PRINT之前添加行@COUNT

Add the line before PRINT @COUNT

INSERT INTO #tempStaTable (sta0) VALUES(@varCount)

这篇关于将值放入select语句中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！