I am trying to generate random data for my sql server database. Ive created a stored procedure that takes length of string to be generated but the problem is that it wont allow me to call it inside the insert statement.
我正在尝试为我的sql server数据库生成随机数据。我已经创建了一个存储过程,它需要生成字符串的长度,但问题是它不允许我在insert语句中调用它。
insert into table1 (varchar_data,int_data) value ((sp_GenerateRandomString 4),CAST(RAND() * 10 AS INT) % 6 + 1 )
also this is not working
这也行不通
insert into table1 (varchar_data,int_data) select ((sp_GenerateRandomString 4),CAST(RAND() * 10 AS INT) % 6 + 1 )
2 个解决方案
#1
You should use a user defined function instead of a stored procedure. A stored procedure cannot be used in expressions, but functions can.
您应该使用用户定义的函数而不是存储过程。存储过程不能在表达式中使用,但函数可以。
A good introduction can be found here: http://www.sqlteam.com/article/user-defined-functions
可以在这里找到一个很好的介绍:http://www.sqlteam.com/article/user-defined-functions
#2
INSERT INTO ... EXEC sp_procedure
#1
You should use a user defined function instead of a stored procedure. A stored procedure cannot be used in expressions, but functions can.
您应该使用用户定义的函数而不是存储过程。存储过程不能在表达式中使用,但函数可以。
A good introduction can be found here: http://www.sqlteam.com/article/user-defined-functions
可以在这里找到一个很好的介绍:http://www.sqlteam.com/article/user-defined-functions
#2
INSERT INTO ... EXEC sp_procedure