从前端将XML发送到存储过程,该过程将其解析为具有两列的临时表:
| DataName | DataValue |
1| blahblah | datadata |
2| ... | ... |
3| ... | ... |
4| ... | ... |
从这个临时表中,我将基于DataName列中的字符串将DataValue列中的值设置为变量。例如:
SELECT @blahblah= IsNull(DataValue,0)
FROM #XMLTempTable
WHERE DataName = 'blahblah'
SELECT @userid = IsNull(DataValue,0)
FROM #XMLTempTable
WHERE DataName = 'userid'
SELECT @value = IsNull(DataValue,0)
FROM #XMLTempTable
WHERE DataName = 'value'
有没有一种更好的方法可以使我从临时表中选择值,而不是每次执行一次选择?
最佳答案
您可以使用CASE WHEN .. END
实现它
SELECT @blahblah = CASE WHEN DataName = 'blahblah' THEN IsNull(DataValue,0) ELSE 0 END,
@userid = CASE WHEN DataName = 'userid' THEN IsNull(DataValue,0) ELSE 0 END,
@value = CASE WHEN DataName = 'value' THEN IsNull(DataValue,0) ELSE 0 END
FROM #XMLTempTable
WHERE DataName in ('blahblah', 'userid', 'value')